Check if SQL Server has an INSERT OR IGNORE or an INSERT ... ON DUPLICATE KEY UPDATE statement. If present, the former will insert all the rows except those that have a primary or unique key conflict. The latter would do the same thing, except it allows you to specify an UPDATE statement for those conflicting rows, rather that just ignoring them. There's also an INSERT OR REPLACE, which would insert missing rows, and delete and resinsert conflicting rows. Might be workable, but will cause hella issues if you've got foreign keys with CASCADE DELETE on them.
If you don't have any of those available to you, you could use your existing CSV file, and just load it into a temporary table. Then you can do a INSERT INTO ... SELECT ... WHERE NOT EXISTS style query to pull in the new stuff, and some other mumbo-jumbo to update existing rows. cheers, barneyb On 11/18/05, Michael Grove <[EMAIL PROTECTED]> wrote: > I need to insert multiple rows into a SQL database from an Access Database. > Basically I have an ecommerce site with a bunch of products in it. We would > like to use these products in another application. I am going to run the > update nightly. After the first update the system will need to check and see > if the product id from ecommerce site exists in the products table of the > other system. If so it needs to skip it, if not it needs to add it. > > I seen somthing about a INSERT SELECT statment, but do not know if it is > possible to use this since I am in two different databases and two different > Data Sources. > > I think I can write a loop and if statment but thing that with the number of > products, we have it may bog the system down. Currently we only have about > 800 but this will triple in a few months. > > My other option is that I can and already am doing a CSV export, but how do I > import this and how do I prevent duplicates? > > any help would be much appreciated. > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:224711 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
