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. Select the correct answer from above options

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