0

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ctrl_Alt_Defeat
  • 3,933
  • 12
  • 66
  • 116
  • 4
    just stating the obvious, but if the tables are exactly the same, and they're in the same database, why not store the information in the same table and simply use a bit flag to indicate the record is submitted/not submitted. With a single set of tables instead of 2 sets, it will save you a lot of unnecessary code if you do it this way. Also, imagine down the line, the table structure changes, you then have 2 places to make the change as well as the code that ports records between the tables. – Tanner Aug 04 '17 at 13:16
  • 1
    I would urge you to consider the suggestion from @Tanner. But if that isn't an option what are you expecting for an answer here? Since we have essentially zero information about all anybody could respond is that you need to insert the parent information first and then the child information. – Sean Lange Aug 04 '17 at 13:24
  • @Tanner - yeah this would have been the ideal solution but unfortunately, the DB Design was already completed before I joined the project – Ctrl_Alt_Defeat Aug 04 '17 at 13:26
  • 1
    @Tanner - while your suggestion is good for 99% of cases, there are situations where a staging table is not a bad idea: 1. there is no restriction on people submitting new applications - you don't want spamming of "production" tables with rubbish. 2. Allow data to be added out of order, which would ordinarily violate FKs and other constraints in production tables. – Alex Aug 04 '17 at 13:29
  • @SeanLange - it was more how to ensure the FK relationships are preserved when copying data that may not have the same ids. So if I have a staging table that has a PK of 4 and this is a FK on another table. When I copy that to Live the ID may be 5 for example - they when I copy the table that had the FK of 4 - how do I determine what it was copied to – Ctrl_Alt_Defeat Aug 04 '17 at 13:29
  • Well since you have provided zero details about your table structure or what you are trying to accomplish how could anybody have a chance at helping? There are a number of ways this could be solved but they all require details about the tables and the process, neither of which we have here. – Sean Lange Aug 04 '17 at 13:32
  • @Alex i'd prefer maintainable code/database over the 1% of cases you indicate. With point 1. if required have a cleanse routine to run regularly to remove incomplete submissions that age away. We have something like this on a product I work on and it works well. Point 2. i don't really understand. – Tanner Aug 04 '17 at 13:33
  • 1
    @Tanner - I am not starting an argument with you, merely pointing out that there are situations where OP's approach is viable. We know nothing about his system, so there is not point is starting ideological wars about what is better. – Alex Aug 04 '17 at 13:41
  • @SeanLange i included an example of two of my tables and the relationship between them - I'm sure I would be downvoted for providing a full schema of 50+ tables - what extra information are you looking for? – Ctrl_Alt_Defeat Aug 04 '17 at 14:03

1 Answers1

1

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?

Alex
  • 4,885
  • 3
  • 19
  • 39
  • @Ctrl_Alt_Defeat - the above is a prototype. If you need more specific example, let me know. Note: I have also updated the answer: you need to use `MERGE` instead of update if you want a direct mapping. – Alex Aug 04 '17 at 14:22
  • Thanks - will give this a go and let you know - I dont necessarily need a direct mapping - so in the Live Tables I dont mind if the Ids match what they were in the Staging Tables – Ctrl_Alt_Defeat Aug 04 '17 at 14:31