Geek Answers Handbook

Transact-sql insert in two tables at the same time?

Well, that will sound weird, but can you insert two tables into the join?

I have table A and table B with foreign key A. Now I want to copy several records of table A into one query + a copy of the records in table B with foreign key in the records that are copied to table A (but as new foreign identifiers).

I hope someone understands me. Another option is to use a cursor, but if possible, I would like to avoid this.

+4
source share
4 answers

Another option is two queries.

First copy the entries with primary keys, then copy the entries using foreign keys.

+3
source

Here is an example if you are trying to "insert into both tables" at once.

view_model_01

First prepare, create tables, etc.

CREATE TABLE Tbl_A ( Tbl_A_ID int NOT NULL PRIMARY KEY identity(1,1) ,A1 varchar(20) ,A2 varchar(20) ,A3 varchar(20) ) GO CREATE TABLE Tbl_B ( Tbl_B_ID int NOT NULL PRIMARY KEY identity(1,1) ,Tbl_A_ID int NOT NULL ,B1 varchar(20) ,B2 varchar(20) ) GO ALTER TABLE TBL_B ADD CONSTRAINT FK1_B FOREIGN KEY ( Tbl_A_ID ) REFERENCES TBL_A ( Tbl_A_ID ) GO

Now an idea of ​​these two tables

 CREATE VIEW vAB (A1, A2, A3, B1, B2) AS SELECT a.A1, a.A2, a.A3, b.B1, b.B2 FROM Tbl_A AS a JOIN Tbl_B AS b ON a.Tbl_A_ID = b.Tbl_a_ID GO

And instead of insert trigger in view

 CREATE TRIGGER trigAB ON vAB INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON DECLARE @aid int SET @aid = coalesce((SELECT max(Tbl_A_ID) FROM Tbl_A),0) SET IDENTITY_INSERT Tbl_A ON ; WITH abc AS( SELECT A1, A2, A3, B1, B2, row_number() OVER(ORDER BY a1,a2,a3,b1,b2) AS rn FROM INSERTED ) INSERT INTO Tbl_A ( Tbl_A_ID, A1, A2, A3 ) SELECT @aid + [rn], A1, A2, A3 FROM abc SET IDENTITY_INSERT Tbl_A OFF ; WITH abc AS( SELECT A1, A2, A3, B1, B2, row_number() OVER(ORDER BY a1,a2,a3,b1,b2) AS rn FROM INSERTED ) INSERT INTO Tbl_B ( Tbl_A_ID, B1, B2 ) SELECT @aid + [rn], B1, B2 FROM abc END

So now you can:

 INSERT INTO vAB ( a1, a2, a3, b1, b2 ) VALUES ('a1_1', 'a2_1', 'a3_1', 'b1_1', 'b2_1') ,('a1_2', 'a2_2', 'a3_2', 'b1_2', 'b2_2') ,('a1_3', 'a2_3', 'a3_3', 'b1_3', 'b2_3') ,('a1_4', 'a2_4', 'a3_4', 'b1_4', 'b2_4') ,('a1_5', 'a2_5', 'a3_5', 'b1_5', 'b2_5')

Confirm with:

 SELECT * FROM vAB ;
+1
source

Well, that will sound weird, but can you insert into the join of two tables?

The short answer is no; You can only insert one table at a time.

The longer answer is that you can lure it with a view or triggers, but if you are going to go that far, then why not just use a stored procedure (which will be easier to write, understand, and maintain)?

Instead of a single statement, in terms of performance and reliability, it looks like you really want to use a single transaction. There are also some methods that allow you to handle multi-line inserts without using cursors.

For more information about your specific operation, this will help if you can post some sample code.

+1
source

No cursors pls ...

You can simply use the INSERT trigger for A, which inserts into B. You just run a query on A, the trigger will fire for each insert.

0
source

More articles:


All Articles