1

I need to Insert multiple rows in a table TableA where primary key is Int auto-generated, followed create records in TableB (1:1) relationship, using the primary key from TableA

TableA

IdA {PK}: int
name : nvchar

TableB

IdB {PK}: int
IdA {FK}: int
CreatedDate : DateTime
ExpriyDate:   DateTime

I want to perform above task under one script

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
K.Z
  • 5,201
  • 25
  • 104
  • 240
  • If there's something in TableA you can match against your insert data, you can use the output clause to output inserted PK and some field which helps you join these two together into a temporary table and then continue with table B. If there's no such field in TableA, you can use a merge statement to output TableA.PK and some source field which doesn't exist in TableA and then continue with above – siggemannen Mar 15 '23 at 10:49
  • If you don't have triggers in table B, you might be able to insert into both tables at the same time using the merge with output, but this is for advanced use only – siggemannen Mar 15 '23 at 10:50
  • In my case there is no record exist in either table and will be new records in both table – K.Z Mar 15 '23 at 10:54
  • do you have example script for it? – K.Z Mar 15 '23 at 10:55

1 Answers1

3

You can use MERGE together with OUTPUT clause. OUTPUT clause in MERGE allows to access columns from both source and destination tables.

Query

MERGE can insert, update and delete, but we need only simple insert, so the join criteria is always false (1=0).

MERGE INTO TableA AS Dest
USING
(
    SELECT name, CreatedDate, ExpiryDate
    FROM #SomeSourceData
) AS Src
ON (1 = 0)
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (name)
VALUES
    (Src.name)
OUTPUT inserted.IdA, Src.CreatedDate, Src.ExpiryDate
INTO TableB(IdA, CreatedDate, ExpiryDate)
;

Here I assume that TableA.IdA is IDENTITY, so this column is not explicitly listed in the INSERT statement. Same for column TableB.IdB.

For each row in #SomeSourceData there will be one row inserted into TableA and one corresponding row inserted into TableB.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • In above example, where `inserted` define? code doesn't recognised – K.Z Mar 15 '23 at 11:47
  • so above code will allow me to insert new record for `TableA` and `TableB` same time with additional newly created `TableA` primary key in `TableB` as foreign Key? – K.Z Mar 15 '23 at 11:48
  • It's not really at the same time but it can be done in a single transaction. Note that only `Merge` allows you to access both inserted and source data at the same output clause. – Zohar Peled Mar 15 '23 at 12:15
  • thanks for confirming.. also where to define inserted in the code? cannot recognise `inserted.IdA` – K.Z Mar 15 '23 at 12:17
  • @K.Z, `inserted` table is defined in / by the `OUTPUT` clause. This is a table that has rows that were inserted by the previous `MERGE` statement. See the docs for the `OUTPUT` clause for details. – Vladimir Baranov Mar 15 '23 at 12:27
  • Yes, @K.Z, this `MERGE` statement with the `OUTPUT` clause inserts rows into both `TableA` and `TableB`. – Vladimir Baranov Mar 15 '23 at 12:33