0

Background: I have a Stored Procedure whose purpose is to "snapshot" the data from "live tables" into the "snapshot tables". I'm passing in table-valued-parameters for performance reasons. The data from the "live tables" are hierarchical in nature (grandparent-parent-child). The database schema is fixed, as of now. Actually it would be easy to fix this problem if I have the liberty to add one more column, but for now I am not allowed to.

Problem: Currently I am stuck at the ChildSnapshot table. I don't know how to get the value of the SnapshotParentId given the database schema. I am not allowed to add "OrigParentId" at the ParentSnapshot table. Please check my stored proc to see where I am stuck.

So these are the "live tables":
(I put A,B,C,X,Y,Z for id columns instead of numbers to easily demonstrate the relationship between tables)

Table: GrandParent
╔════╦══════╗
║ id ║ Data ║
╠════╬══════╣
║ A  ║ Kris ║
╚════╩══════╝
Table: Parent
╔════╦══════╦══════════╗
║ id ║ Data ║ GrannyId ║
╠════╬══════╬══════════╣
║ B  ║ Kim  ║ A        ║
╚════╩══════╩══════════╝
Table Child
╔════╦═══════╦══════════╗
║ id ║ Data  ║ ParentId ║
╠════╬═══════╬══════════╣
║ C  ║ North ║ B        ║
╚════╩═══════╩══════════╝

While these are the "live" tables, containing my expected results as well: 

Table GrandParentSnapshot:  
╔════╦═══════╦══════════╗
║ id ║ Data  ║ OrigId   ║
╠════╬═══════╬══════════╣
║ X  ║ Kris  ║ A        ║
╚════╩═══════╩══════════╝

Table ParentSnapshot:  
╔════╦═══════╦════════════════════╗
║ id ║ Data  ║ SnapshotGrannyId   ║
╠════╬═══════╬════════════════════╣
║ Y  ║ Kim   ║ X                  ║
╚════╩═══════╩════════════════════╝

Table ChildSnapshot:  
╔════╦═══════╦════════════════════╗
║ id ║ Data  ║ SnapshotParentId   ║
╠════╬═══════╬════════════════════╣
║ Z  ║ North ║ Y                  ║
╚════╩═══════╩════════════════════╝

This is my stored procedure:

Parameters
@tvpGrandparents -- columns: Data and OrigId
@tvpParents      -- columns: Data and GrannyId (and ???)
@tvpChildren     -- columns: Data and ParentId (and ???)

BEGIN
-- Works fine for GrandParentSnapshot, especially because there is an OrigId column in the table schema
DECLARE @InsertedGrannies TABLE( snapGranId, origId)

INSERT INTO GrandParentSnapshot (Data, OrigId)
OUTPUT Inserted.Id, Inserted.OrigId INTO @InsertedGrannies
SELECT Data, OrigId FROM @tvpGrandparents 

-- Insert Also works fine for ParentSnapshot
INSERT INTO ParentSnapshot (Data, OrigId)
SELECT parents.Data, insertedGrannies.snapGranId FROM @InsertedGrannies insertedGrannies
JOIN @tvpParents parents ON insertedGrannies.origId = parents.GrannyId

-- How do I do it for the ChildSnapshot??
-- I don't know how to come up with the SnapshotParentId
-- Is there anything I can OUTPUT from the Insert of ParentSnapshot table for me to wire up some link?
-- temp tables? any other ideas?

END

Hope someone can give some suggestion...sorry for my length question.

remondo
  • 318
  • 2
  • 7
  • You're almost there. You can `output Inserted.Id, Inserted.Origid INTO @InsertedParents` (`declare @InsertedParents as table ( snapParentId Int, origId Int );`) in the second `insert`. For the `ChildSnapshot` you need to join `@InsertedParents` and `@tvpChildren` like you did for the parents. (Or my eyes are more crossed than I think.) – HABO Feb 18 '19 at 20:11
  • Thanks for answering HABO. However, @InsertedParents or ParentSnapshot table does not have a column for its original ID. If only I could add this column (I am not allowed to change the schema at this point of the project). – remondo Feb 19 '19 at 02:13
  • 1
    Sorry, my bad. Next up is the old shell game. The first `insert` is fine. Modify the second to store the original `id` from `tvpParents` in the `SnapshotGrannyId` column, thus allowing you to `output` it. Use the output table, `@InsertedGrannies` and the original `tvpParents` to `update ParentSnapshot` with the correct `SnapshotGrannyId` values. The output table also gives you the fixup data you need for the children's parents. Wrap the whole thing in a transaction to prevent anyone from peeking while you're halfway done. (That may require a high isolation level or using locking hints.) – HABO Feb 19 '19 at 04:16
  • 2
    Check out the duplicate in the link above. The idea is that instead of using `insert`, you use `merge` to insert the records to the `parent` table. By using `merge`, you can have access to both `inserted` and `source` data in the `output` clause, so you can fill a mapping table just like you did with `@InsertedGrannies` - from that point on it's simple. – Zohar Peled Feb 19 '19 at 05:40
  • @HABO Appreciate your suggestion. So basically you are suggesting for me to insert a temporary value at first so that I can output it and move on, then later update the temporary values with the final values? If yes, that was one of the options i was considering also :-) – remondo Feb 19 '19 at 07:06
  • @ZoharPeled I tried SQL Merge and it works for a simple table that I tried. I think I will use this :-) – remondo Feb 19 '19 at 07:07
  • Just for completeness, the other ugly approach that i was trying is to assign some sequence numbers (0 to n) into the tvp's. To link the inserted id's output to the sequence numbers, I will subtract the inserted id from the first inserted id. This should match the sequence numbers. Yeah, it's an ugly approach hehe – remondo Feb 19 '19 at 07:11
  • 1
    @remondo Yes, just "borrow" a column briefly so that you can use `output` with a value that wouldn't usually be available, then `update` to slide the correct value into place. – HABO Feb 19 '19 at 13:43
  • 1
    @HABO OK. For now I am done implementing using SQL Merge. I think it's cleaner. Your approach can be considered in other SQL frameworks (e.g. MYSQL?) that do not support this command. Thanks again! – remondo Feb 20 '19 at 06:52

0 Answers0