To Insert/Update in Main Table from Staging Table in SQL Server 2008

join sqlbulkcopy sql-server-2008

Question

After the data is bulk-inserted into a staging table, I need to do two actions:

  1. Transformations
  2. Update/Insert in the main table

Is the JOINS tool the best option, or is there a faster or more effective way to do these tasks?

1
1
8/21/2011 12:33:39 PM

Accepted Answer

I think I'd take the following action:

  • alter yourself on that stage table (add additional columns as needed etc.)
  • Use the 2008 SQL ServerMERGE command, which is well designed for only this task: update some current rows, insert some new rows, to do the INSERT/UPDATE into the real data table (and possibly delete some old "orphaned" rows). Almost any insert, update, and delete case may be handled by a single command in a single call.

Check out some excellent articles on using theMERGE command:

2
8/21/2011 12:46:08 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow