Hola!

I'm currently building a site that uses an external database to store all the product details, and an internal database that will act as a cache so that we don't have to keep hitting the external database to retrieve the products every time a customer requests a list.

What I need to do is retrieve all these products from External and insert them into Internal if they don't exist - if they do already exist then I have to update Internal with new prices, number in stock etc.

I was wondering if there was a way to insert / update these products en-mass without looping through and building a new insert / update query for every product - there could be thousands at a time!

Does anyone have any ideas or could you point me in the right direction?

I'm thinking that because I need to check if the products exist in a different data store than the original source, I don't have a choice but to loop through them all.

Cheers,
G.
 

You can create INSERT Triggers on the products table so that if the products already exists you just update its information. For example:

create table myProducts(pid int,pname sysname, price decimal(9,2))
go
create trigger trg_InsOrUptPro on myProducts instead of insert
as
if (exists(select 1 from myProducts m,inserted i where m.pid=i.pid))
update myProducts set price=i.price
from myProducts m,inserted i
where m.pid=i.pid
else
insert into myProducts select * from inserted
go

insert into myProducts select 1,'Telephone',99.9

insert into myProducts select 1,'Telephone',88.8

insert into myProducts select 2,'Computer',6000.00

select * from myProducts

However the performance may be not so good for using triggers. I suggest you create a clustered index on some column like pid to improve query performance.

Ah that's fantastic, I never even thought about using triggers!

Thanks, Iory_Jay.

G.