SQL Server 2008 - To Insert/Update in Main table from Staging table

join sqlbulkcopy sql-server-2008

Question

Once the data is imported in a Staging table through Bulk Insert, then I need to do two steps:

  1. Transformations
  2. Insert/Update in Main table

Is the JOINS are the best tool OR there is something quicker/more efficient to perform these tasks?

Accepted Answer

I would probably do something like this:

  • make your transformations on that staging table (add additional columns as needed etc.)
  • do the INSERT/UPDATE into the actual data table using the SQL Server 2008 MERGE command which is ideally suited for just this - update some existing rows, insert some new rows (and possibly delete some old "orphaned" rows). It's a single command which can handle just about all insert, update, delete scenario in a single call

See some great articles on how to use the MERGE command:



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why