Insert Rows into Multiple Tables in a Single Query, Selecting from an Involved Table

INSERT rows into multiple tables in a single query, selecting from an involved table

Final version

... after some more info from OP. Consider this demo:

-- DROP TABLE foo; DROP TABLE bar;

CREATE TEMP TABLE bar (
 id serial PRIMARY KEY  -- using a serial column!
,z  integer NOT NULL
);

CREATE TEMP TABLE foo (
 id     serial PRIMARY KEY  -- using a serial column!
,x      integer NOT NULL
,y      integer NOT NULL
,bar_id integer UNIQUE NOT NULL REFERENCES bar(id)
);

Insert values - bar first.

It would be very helpful if you provided test data in your question like this!

INSERT INTO bar (id,z) VALUES
 (100, 7)
,(101,16)
,(102,21);

INSERT INTO foo (id, x, y, bar_id) VALUES
 (1, 3,4,100)
,(2, 9,6,101)
,(3,18,0,102);

Set sequences to current values or we get duplicate key violations:

SELECT setval('foo_id_seq', 3);
SELECT setval('bar_id_seq', 102);

Checks:

-- SELECT nextval('foo_id_seq')
-- SELECT nextval('bar_id_seq')
-- SELECT * from bar;
-- SELECT * from foo;

Query:

WITH a AS (
    SELECT f.x, f.y, bar_id, b.z
    FROM   foo f
    JOIN   bar b ON b.id = f.bar_id
    WHERE  x > 3
    ),b AS (
    INSERT INTO bar (z)
    SELECT z
    FROM   a
    RETURNING z, id AS bar_id
    )
INSERT INTO foo (x, y, bar_id)
SELECT a.x, a.y, b.bar_id
FROM   a
JOIN   b USING (z);

This should do what your last update describes.

The query assumes that z is UNIQUE. If z is not unique, it gets more complex. Refer to Query 2 in this related answer for a ready solution using the window function row_number() in this case.

Also, consider replacing the 1:1 relation between foo and bar with a single united table.

---

Data modifying CTE

Second answer after more info.

If you want to add rows to foo and bar in a single query, you can use a data modifying CTE since PostgreSQL 9.1:

WITH x AS (
    INSERT INTO bar (col1, col2)
    SELECT f.col1, f.col2
    FROM   foo f
    WHERE  f.id BETWEEN 12 AND 23 -- some filter
    RETURNING col1, col2, bar_id  -- assuming bar_id is a serial column
    )
INSERT INTO foo (col1, col2, bar_id)
SELECT col1, col2, bar_id
FROM   x;

I draw values from foo, insert them in bar, have them returned together with an auto-generated bar_id and insert that into foo. You can use any other data, too.

Here is a working demo to play with on sqlfiddle.

---

Basics

Original answer with basic information before clarifications.

The basic form is:

INSERT INTO foo (...)
SELECT ... FROM foo WHERE ...

No parenthesis needed.
You can do the same with any table

INSERT INTO foo (...)
SELECT ... FROM bar WHERE ...

And you can join to the table you insert into in the SELECT:

INSERT INTO foo (...)
SELECT f.col1, f.col2, .. , b.bar_id
FROM   foo f
JOIN   bar b USING (foo_id);  -- present in foo and bar

It's just a SELECT like any other - that can include the table you are inserting into. The rows are first read, and then inserted.

How to insert data in multiple tables using single query in SQL Server?

To me - this seems a lot cleaner, and it also will be a lot simpler to understand (and maintain!) in the future:

-- check to see if your data already exists
IF NOT EXISTS (SELECT * 
               FROM search_results 
               WHERE company_id = 4 AND link = 'https://test.com')
BEGIN TRY
  BEGIN TRANSACTION
    -- if not -> insert into the first table
    INSERT INTO search_results (company_id, link, title, domain)   
    VALUES (4, 'https://test.com', 'title', 'test.com');

    -- grab the last identity value from that previous INSERT       
    DECLARE @LastId INT;
    
    SELECT @LastId = SCOPE_IDENTITY();
    
    -- insert into the second table
    INSERT INTO corporate_statements (statement_link_id, corporate_statement) 
    VALUES (@LastId, 1);

    COMMIT;
END TRY
BEGIN CATCH
    -- in case of an error rollback the full transaction
    ROLLBACK;
END CATCH;

and you're done. Or am I missing something? I think this would be doing what you're described in the intro of your post - not necessarily what you're showing in your code...

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

Best way to insert into multiple tables in a cluster

The INSERT ALL statement can insert into multiple tables in a single statement, but I don't think it can be used to insert data selected from another table.

Yes it can. There are examples of that in the documentation. Here you can do something like:

insert all
into customer_name (customer_id, first_name, last_name)
            values (customer_id, first_name, last_name)
into customer_dob (customer_id, dob)
           values (customer_id, dob)
select customer_id, first_name, last_name, dob
from customer;

Demo using a cut-down version of your table and one dummy row:

create table customer (
  customer_id number(38),
  first_name varchar2(20),
  last_name varchar2(20),
  dob date
);

insert into customer (customer_id, first_name, last_name, dob)
values (42, 'Alex', 'Poole', date '1972-01-01');

and a cluster with two tables (though the cluster isn't really relevant):

create cluster customer_cluster (customer_id number(38));

create index customer_cluster_idx on cluster customer_cluster;

create table customer_name (
  customer_id number(38),
  first_name varchar2(20),
  last_name varchar2(20)
)
cluster customer_cluster (customer_id);

create table customer_dob (
  customer_id number(38),
  dob date
)
cluster customer_cluster (customer_id);

then you can do:

insert all
into customer_name (customer_id, first_name, last_name)
            values (customer_id, first_name, last_name)
into customer_dob (customer_id, dob)
           values (customer_id, dob)
select customer_id, first_name, last_name, dob
from customer;

2 rows inserted.

select * from customer_name;

CUSTOMER_ID FIRST_NAME           LAST_NAME           
----------- -------------------- --------------------
         42 Alex                 Poole               

select * from customer_dob;

CUSTOMER_ID DOB       
----------- ----------
         42 1972-01-01

Insert into table with multiple select including select distinct from two tables

I think you want insert . . . select with join:

insert into Table3 (companyid, certno, expdate, createddate, is_null)
    select distinct t2.companyid, t1.certno, t1.expdate, getdate(), 1
    from Table1 t1 join
         Table2 t2
         on t1.company = t2.company
    where t1.company like '%ABC%';

You can remove the where clause to insert all companies in one insert.

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

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.

---

Related Topics