Did you ever get your Excel to MSSQL merge working?
in [Programming SQL]
|
Prev: Inserting data into denormalized table
Next: set identity_insert not works for insert statement on SQL2008
From: Mud on 19 Jul 2010 16:18 Just curious if you ever got your Excel to MSSQL merge working and what method you used. Sam wrote: Need help with bulk INSERT/UPDATE 24-Nov-09 Hi, In my ecommerce site, I want to give merchants a way to insert/update data in bulk i.e. ProductID and Price. It makes sense to use Excel sheets for this purpose. If I were to read everthing line by line from the Excel sheet and call a stored procedure, each transaction would generate hundreds of database hits. What is the best way to handle this? Another important feature is that if the vendor already has a ProductID in the database, the new data should overwrite the old. So it will be a MERGE statement in the backend. I'd appreciate some pointers in handling this. Previous Posts In This Thread: On Tuesday, November 24, 2009 9:23 PM Sam wrote: Need help with bulk INSERT/UPDATE Hi, In my ecommerce site, I want to give merchants a way to insert/update data in bulk i.e. ProductID and Price. It makes sense to use Excel sheets for this purpose. If I were to read everthing line by line from the Excel sheet and call a stored procedure, each transaction would generate hundreds of database hits. What is the best way to handle this? Another important feature is that if the vendor already has a ProductID in the database, the new data should overwrite the old. So it will be a MERGE statement in the backend. I'd appreciate some pointers in handling this. On Tuesday, November 24, 2009 9:59 PM Michael Coles wrote: You can give them an SSIS package (or kick one off), or you might You can give them an SSIS package (or kick one off), or you might consider using bcp or the BULK INSERT statement if you can get them to save the data in a supported format (or if you can convert it to a supported format before the load). Regardless of the tool you use to get the data into the database I would recommend loading the data into a staging table in the database first, and then use MERGE or other DML statements to update the target table(s). By loading the data into a staging table first you can perform additional validations and any scrubbing/cleansing necessary before you commit it to your production tables; it also gives you an opportunity to stop the process if something in the data is not quite right before your production tables are modified. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- Submitted via EggHeadCafe - Software Developer Portal of Choice Assemblies in Folder Debug Build Checker http://www.eggheadcafe.com/tutorials/aspnet/d7de5fe1-6155-4e81-96e1-9806fd69d760/assemblies-in-folder-debug-build-checker.aspx |