CODEWITHSUNDEEP
sql-serverdatabase
Ctrl_Alt_Defeat
Ctrl_Alt_Defeat

Reputation: 4009

Copy data from SQL Server Staging to Live tables

I have a SQL Server database with staging tables where a user entered data gets saved until they finally submit the application form at which point the data gets copied to live tables.

So I have a table called ApplicationStaging with columns:

ApplicationID        1
UserID               2
CustomerName         'Joe Bloggs'
//More columns removed for brevity

Then I have a table called ItemStaging with columns:

ItemID               1
ApplicationID (FK)   1
ItemName             'Car'
//More columns removed for brevity

Then I have my live Application table and live Item table which at the minute contain exactly the same column names as the staging tables. I have around 20 tables like this and there are FK relationships between some different tables. what is the best way to use a stored procedure to copy the data from the staging tables to the live tables while recreating the FK relationships (noting that the IDs from staging to submitted may be different depending on when an end user submits the application versus when it was saved).

I was thinking one of the things I will need to do on the live tables is add a StagingID column and set that so I will be able to have a link back to what data was copied into the live table from the staging table

Upvotes: 0

Views: 1080

Answers (1)

Alex
Alex

Reputation: 5157

Two solutions:

Use Sequences to generate ids for both production and staging tables, then you do not have to worry about ID mapping.

If sequences are a no go then use OUTPUT clause to extract ID mappings:

INSERT INTO Parent
OUTPUT INSERTED.ID, StagingParent.ID INTO @ParentIDMapping( PID, SPID )
SELECT *
FROM StagingParent

INSERT INTO Child
OUTPUT INSERTED.ID, Child.ID INTO @ChildIDMapping
SELECT PID AS FK, * 
FROM StagingChild AS SC
    INNER JOIN @ParentIDMapping AS PID ON SC.FK = SPID

See examples at the bottom of this MSDN document on how to use OUTPUT.

Update: since INSERT does not allow you to output columns that were not actually inserted, you need use MERGE. See: Is it possible to for SQL Output clause to return a column not being inserted?

Upvotes: 1

Related Questions