Merge SQL INSERT with Reference Table

Question

I need to synchronize two tables.

TableA

Id    Name  

TableB

Id  Name RefID --It's a Foreign key, defined as primary key in Table "TableReference"

TableReference

RefID -- Identity Column, auto increment

I need to merge TableA and TableB in such a way that on each insert in TableB, a value should be inserted into TableReference and that inserted value should be copied into RefId column of TableB.

What I am doing?

I am using SSIS hence I need to have a SSIS based solution OR SQL based solution. I know how to merge tables using Merge SQL command but I am not able to insert a value into TableRef and copy it back to TableB. Could not figure out how i can accomplish this. SQL user-defined functions don't allow INSERT so I can't use it.

Merge TabaleB T

Using Table A S

On S.Id=T.Id

WHEN MATCHED THEN

UPDATE

T.ID=S.ID,

T.NAME=S.NAME

WHEN NOT MATCHED BY TARGET THEN

INSERT(S.ID,S.NAME, {Somehow here i need a function call that inserts in TableRef and   Returns SCOPE_IDENTITY})

The problem is T-SQL functions don't allow INSERT and a stored procedure can't be called here as Merge doesn't allow any TSQL thing other than INSERT after WHEN NOT MATCHED BY TARGET THEN.

Answer 1

If you are able to add a trigger in your Table2 then you should use INSERTED row.

Refer to this video to learn MERGE STATEMENT in SQL.

 Use this code:

CREATE TRIGGER dbo.tr_Table2

ON dbo.Table2

FOR INSERT

AS

BEGIN

    SET NOCOUNT ON;

 INSERT TableReference  DEFAULT VALUES;  

    DECLARE @RefId INT;

    SELECT @RefId  = SCOPE_IDENTITY();

    UPDATE ta

        SET ta.Ref_Id = @Ref_Id

    FROM dbo.TableB AS ta 

        INNER JOIN INSERTED AS i1

            ON i1.Id= ta.Id

END

GO