CODEWITHSUNDEEP
sqlpostgresql
Chickenfresh
Chickenfresh

Reputation: 365

A sql query to create multiple rows in different tables using inserted id

I need to insert a row into one table and use this row's id to insert two more rows into a different table within one transaction. I've tried this

begin;
insert into table default values returning table.id as C;
insert into table1(table1_id, column1) values (C, 1);
insert into table1(table1_id, column1) values (C, 2);
commit;

But it doesn't work. How can I fix it?

updated

Upvotes: 0

Views: 967

Answers (2)

404
404

Reputation: 8572

You need a CTE, and you don't need a begin/commit to do it in one transaction:

WITH inserted AS (
    INSERT INTO ... RETURNING id
)
INSERT INTO other_table (id)
SELECT id
FROM inserted;

Edit: To insert two rows into a single table using that id, you could do that two ways:

  • two separate INSERT statements, one in the CTE and one in the "main" part
  • a single INSERT which joins on a list of values; a row will be inserted for each of those values.

With these tables as the setup:

CREATE TEMP TABLE t1 (id INTEGER);
CREATE TEMP TABLE t2 (id INTEGER, t TEXT);

Method 1:

WITH inserted1 AS (
        INSERT INTO t1
        SELECT 9
        RETURNING id
), inserted2 AS (
        INSERT INTO t2
        SELECT id, 'some val'
        FROM inserted1
        RETURNING id
)
INSERT INTO t2
SELECT id, 'other val'
FROM inserted1

Method 2:

WITH inserted AS (
        INSERT INTO t1
        SELECT 4
        RETURNING id
)
INSERT INTO t2
SELECT id, v
FROM inserted
CROSS JOIN (
        VALUES
        ('val1'),
        ('val2')
) vals(v)

If you run either, then check t2, you'll see it will contain the expected values.

Upvotes: 1

Banu Priya
Banu Priya

Reputation: 61

Please find the below query:

insert into table1(columnName)values('stack2');

insert into table_2 values(SCOPE_IDENTITY(),'val1','val2');

Upvotes: 0

Related Questions