1

I think i have myself in a bit of a spin on this one. i have tried a few different things, it really shouldn't be this complicated. Perhaps someone can help me?

I have a table of data (source) which i need to copy over to 2 other tables (target1 and target2). These tables have a FK constraint between each other. So i need to OUTPUT the id of the inserted record into the second table. The closest i have gotten is using a MERGE, but it is complaining that:

OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship.

Any assist on the following would be welcome, or if i am totally on the wrong path i would appreciate any direction you can give:

BEGIN TRAN;
MERGE [DBTarget].[dbo].[TargetTable1] AS T

USING [DBSource].[dbo].[SourceTable] AS S ON (T.[Col1] = S.[Col1])

WHEN NOT MATCHED BY TARGET

    THEN INSERT([Col1] -- Target column
               ,[Col2] -- Target column
               ,[Col3] -- Target column
               ,[Col4])-- Target column

         VALUES(S.[Col1] -- Source column
               ,(SELECT [Col9] FROM [DBSource2].[dbo].[SourceTable2] YI WHERE YI.[Col1] = S.[Col1]) -- Here i am reaching out to another table for a column value for the inserted row
               ,S.[Col3] -- Source column
               ,S.[Col4])-- Source column

OUTPUT
    inserted.Id -- Value inserted to: [DBTarget].[dbo].[TargetTable2].[Col1]
    ,S.[Col2]   -- Value inserted to: [DBTarget].[dbo].[TargetTable2].[Col2]
    ,S.[Col3]   -- Value inserted to: [DBTarget].[dbo].[TargetTable2].[Col3]
    ,NULL       -- Value inserted to: [DBTarget].[dbo].[TargetTable2].[Col4]

INTO [DBTarget].[dbo].[TargetTable2] ([Col1]
                                     ,[Col2]
                                     ,[Col3]
                                     ,[Col4]);

ROLLBACK TRAN;  
GO
Nugs
  • 5,503
  • 7
  • 36
  • 57
  • here is an example of hour to use output with a INSERT INTO statement, but the syntax isn't available in merge the way you are doing it anyway. Using Seperate DML statements might be more performant for you too. https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value but your scenario makes me wonder about schema and reasons to do it though I know we don't always get to choose what to do in that regards – Matt Oct 25 '17 at 00:35

0 Answers0