SSIS Staging Table to Normalized form
<p>I could be down the wrong path with this. However, here goes. I am trying to take multiple excel sheets and load them into SQL Server using SSIS. </p>

<p>Excel sheet:</p>

<pre><code>RQ|Descr|PartNum|Manufacturer|...
</code></pre>

<p>I am loading this into a staging table with a couple of derived columns:</p>

<pre><code>RQ|Descr|PartNum|Manufacturer|Origin|DateTime|...
</code></pre>

<p>This is no big deal, I am able to do this easily. However, the problem is how to get the data from the staging table to the correct table and ensuring FK constraints are followed. See below for an illustration.</p>

<p>My goal is to take <code>RQ|Descr|PartNum|Manufacturer|Origin|DateTime|...</code>
and populate multiple tables </p>

<p>[t1]  <code>id|RQ|Descr|Origin|DateTime</code></p>

<p>[t2]  <code>id|t1_id|PartNum|Manufacturer</code></p>

<p>[t3] <code>id|t1_id|...</code></p>

<p><a href="https://i.stack.imgur.com/CD4ff.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CD4ff.png" alt="Image of staging table to production table mapping"></a></p>

<p>I have tried <code>MERGE</code> however I am unsure how to keep the FK relationship.</p>

<pre><code>MERGE INTO spin_item AS targ

USING ssis_stage AS src ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into spin_item:
    INSERT (description, reqqty, price, origin, datetime, exclude, status, siteid, production, repairable)      
    VALUES (src.description, src.rq, src.price, src.origin, GETDATE(), 0, 'N', '', 0, 0)

    -- INSERT into spin_part: 
    OUTPUT inserted.ID, src.manufacturer, src.partnum 
    INTO spin_part (ID, src.manufacturer, src.partnum);
</code></pre>

<p>I have looked into this <a href="https://stackoverflow.com/questions/17531490/ssis-using-multicast-to-enter-data-into-2-related-destinations">SSIS : Using multicast to enter data into 2 RELATED destinations</a> but this is for a one-to-many relationship. So, I am not sure how to populate my t1 table and use the id to populate t2, t3 from the staging table.</p>

<p><strong>EDIT:</strong>   <em>Below, seems to be a working solution. However, I am not sure that it is a good solution.</em></p>

<pre><code>BEGIN
SET IDENTITY_INSERT dbo.spin_item ON

--Insert into spin_item
MERGE INTO spin_item AS targ
USING ssis_stage AS src ON 1=0

WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, description, reqqty, price, origin, datetime, exclude, status, siteid, production, repairable)
    VALUES (src.id, src.description, src.rq, src.price, src.origin, GETDATE(), 0, 'N', '', 0, 0);
SET IDENTITY_INSERT dbo.spin_item OFF

--Insert into spin_part
MERGE INTO spin_part AS targ
USING ssis_stage AS src ON 1=0

WHEN NOT MATCHED BY TARGET AND src.partnum IS NOT NULL THEN
    INSERT (itemid_id, manufacturer, partnum, catalognum, [primary])
    VALUES (src.id, src.manufacturer, src.partnum, src.partnum, 1);

--Insert into spin_stock
MERGE INTO spin_stock AS targ
USING ssis_stage AS src ON 1=0

WHEN NOT MATCHED BY TARGET AND src.stock IS NOT NULL THEN
    INSERT (itemid_id, stocknum)
    VALUES (src.id, src.stock);  


--Insert into spin_collaboration        
MERGE INTO spin_collaboration AS targ
USING ssis_stage AS src ON 1=0

WHEN NOT MATCHED BY TARGET AND src.notes IS NOT NULL THEN
    INSERT (itemid_id, comment, datetime)
    VALUES (src.id, src.notes, GETDATE());

DELETE FROM ssis_stage WHERE id &gt; 0 --Instead of Truncate since auto_increment will reset.

END
</code></pre>