Transact-sql insert in two tables at once?

Question

Ok, this will sound weird, but can you do an insert in a join of two tables?

I have a table A and a table B with a foreign key to A. Now I want to copy multiple records of table A in one query + a copy of the records in table B with foreign key to the records being copied in table A (but as foreign key the new identities).

Hope somebody understands me. My other option is to use a cursor but if possible I would like to avoid this.

Answer 1

Pls no cursors...

You could just use an INSERT trigger on A that inserts into B. You would just run the query on A, the trigger would fire for each insert.

Answer 2

Another option is two queries.

Copy the records with the primary keys first, then copy the records with the foreign keys.

Answer 3

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

First some preparation, 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 a view on 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 an instead of insert trigger on the 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 it is possible to:

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')

Verify with:

SELECT * FROM vAB ;

Answer 4

Ok, this will sound weird, but can you do an insert in a join of two tables?

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

The longer answer is that you can fudge it with a view or triggers, but if you're 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, from a performance and reliability perspective, it sounds like what you really want to do is to use a single transaction. There are also some techniques that allow you to handle multiple-row inserts without resorting to cursors.

For your the details of your specific operation, it would help if you could post some sample code.