CODEWITHSUNDEEP
sql-serverinsert-into
SeeDosRun
SeeDosRun

Reputation: 131

SQL Server: Insert Into 2 Tables in one query

I have seen a few questions similar to this but none gave me the answer I was looking for.

So here is the example

[Table A]:

ID pk/auto-increment
Name
Age
...

[Table B]:

ID pk/auto-increment
FK_A_ID fk
Comment

I have an import of data that contains over 700 rows (and growing)

[Table Import] Name / Age / ... / Comment

Is it possible to use a query similar to:

INSERT INTO [TABLE A] (Name, Age, ...), [Table B] (FK_A_ID, Comments)
   SELECT
       Name, Age, ..., @@IDENTITY, Comment
   FROM
       [TABLE Import]

Or a shorter question, is it possible to insert into 2 tables in the same query referencing the first insert? - when I right it out like that it seems unlikely.

Thanks

Upvotes: 0

Views: 8302

Answers (3)

Vargan
Vargan

Reputation: 1317

In my honest opinion, the best way to do this is create a stored procedure and rollback in case of failure. If you do so, you don't need a transaction because until you supply the "COMMIT" command nothing will be inserted.

Upvotes: 0

Adam Stewart
Adam Stewart

Reputation: 1993

I think you can do it with some temporary tables, and the row_number feature, then perform separate inserts in to TABLE A and TABLE B from the temporary table

UNTESTED

create table source
(
    Name varchar(50),
    age int,
    comment varchar(100)
)

go

insert into source
    (name, age, comment)
values
    ('adam',12,'something'),
    ('steve',12,'everything'),
    ('paul',12,'nothing'),
    ('john',12,'maybe')


create table a
(
    id int identity(1,1) not null,
    name varchar(50),
    age int,
    rowid int
)
go

create table b
(
    id int identity(1,1) not null,
    comment varchar(50),
    fkid int not null
)
go

declare @tempa table
(
    RowID int,
    Name varchar(50),
    age int,
    comment varchar(100)
)
go

insert into @tempa
    (rowid, name, age, comment)
SELECT ROW_NUMBER() OVER(ORDER BY name DESC) AS RowId, 
    name, age, comment 
FROM source
go

insert into a
    (name, age, rowid)
select  name, age, rowid
from    @tempa

insert into b
    (comment, fkid)
select  t.comment,
        a.id as fkid
from    @tempa t inner join a a
        on t.rowid = a.rowid

Upvotes: 0

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You can't. But you can use transaction, like this:

START TRANSACTION;
  INSERT INTO tableA
  SELECT Name, Age, ... FROM tableImport;

  INSERT INTO tableB
  SELECT A.ID, I.Comment
     FROM tableA A INNER JOIN tableImport I
                 ON A.Name = I.Name AND A.Age = I.Age AND ...;-- (if columns not unique)
COMMIT;

Upvotes: 2

Related Questions