Transact-sql insert in two tables at once?

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.

Another option is two queries.

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

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.

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 ;

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.