CODEWITHSUNDEEP
sqlsql-serverdatabase
Ankur Nigam
Ankur Nigam

Reputation: 83

How can I insert multiple row data into two tables simultaneously

Let's say, I've 3 tables A, B and C with following structure.

Table-A

ID - (int) - PrimaryKey - Identity(auto-generated)
DeptCode - (int)

Table-B

ID - (int) - PrimaryKey - Identity(auto-generated)
Data1 - varchar
AID - Foreign Key from Table-A

Table-C

ID - (int) - PrimaryKey - Identity(auto-generated)
Data2 - varchar
Data3 - varchar
BID - Foreign Key from Table-B

My Question: How to insert data in 'Table-B' for all records where "DeptCode=101" in 'Table-A' and corresponding to each newly inserted data in 'Table-B' insert 1 row data in 'Table-C' - In Single query

The problem can be broken down into two parts, inserting multiple row data in 'Table-B' from 'table-A' where criteria is satisfied. It can be done using following script.

INSERT INTO [TableB] ([Data1]) SELECT  NEWID() FROM TableA WHERE [DeptCode] = 101;

For 1 newly added row in Table-B, data can be added to 'Table-C' using following query; not sure how the two queries can be merged - for all the records from 'Table-A'

BEGIN TRANSACTION
DECLARE @DataID int;
INSERT INTO Table2 ([Data1]) VALUES (NewID());
SELECT @DataID = scope_identity();
INSERT INTO Table3 VALUES ('some data', 'some more data', @DataID);
COMMIT

Upvotes: 0

Views: 1883

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

You can use output clause to manage multiple inserted rows. Something like this.

DECLARE @ids table (DataID int);
BEGIN TRANSACTION
INSERT INTO [TableB] ([Data1]) 
output inserted.id into @ids --collect identity id's
SELECT  NEWID() FROM TableA WHERE [DeptCode] = 101;

INSERT INTO Table3 
select 'some data', 'some more data', DataID
from @ids;
COMMIT

Upvotes: 1

Related Questions