CODEWITHSUNDEEP
sqlsql-servert-sqlsql-insert
Manngo
Manngo

Reputation: 16391

Can I insert into multiple related tables in a single statement?

I have two related tables something like this:

CREATE TABLE test.items 
(
    id INT identity(1,1) PRIMARY KEY,
    type VARCHAR(max),
    price NUMERIC(6,2)
);

CREATE TABLE test.books 
(
    id INT PRIMARY KEY REFERENCES test.items(id),
    title VARCHAR(max),
    author VARCHAR(max)
);

Is it possible to insert into both tables using a single SQL statement?

In PostgreSQL, I can use something like this:

--  PostgreSQL:
WITH item AS (INSERT INTO test.items(type,price) VALUES('book',12.5) RETURNING id)
INSERT INTO test.books(id,title) SELECT id,'Good Omens' FROM item;

but apparently SQL Server limits CTEs to SELECT statements, so that won’t work.

In principle, I could use the OUTPUT clause this way:

--  SQL Server:
INSERT INTO test.items(type, price)
OUTPUT inserted.id, 'Good Omens' INTO test.books(id,title)
VALUES ('book', 12.5);

but this doesn’t work if there’s a foreign key involved, as above.

I know about using variables and procedures, but I wondered whether there is a simple single-statement approach.

Upvotes: 0

Views: 70

Answers (1)

George Joseph
George Joseph

Reputation: 5932

You can using dynamic sql as follows. Although its awkward to construct query like this.

CREATE TABLE dbo.items (
    id INT identity(1,1) PRIMARY KEY,
    type VARCHAR(max),
    price NUMERIC(6,2)
);


CREATE TABLE dbo.books (
    id INT PRIMARY KEY REFERENCES dbo.items(id),
    title VARCHAR(max),
    author VARCHAR(max)
);

insert into dbo.books(id,title)
exec ('insert into dbo.items(type,price) output inserted.id,''Good Omen'' VALUES(''book'',12.5)')

Upvotes: 1

Related Questions