INSERT INTO ... FROM SELECT ... RETURNING id mappings
This would be simpler for UPDATE
, where additional rows joined into the update are visible to the RETURNING
clause:
- Return pre-UPDATE column values using SQL only
The same is currently not possible for INSERT
. The manual:
The expression can use any column names of the table named by table_name
table_name being the target of the INSERT
command.
You can use (data-modifying) CTEs to get this to work.
Assuming title
to be unique per query, else you need to do more:
WITH sel AS (
SELECT id, title
FROM posts
WHERE id IN (1,2) -- select rows to copy
)
, ins AS (
INSERT INTO posts (title)
SELECT title FROM sel
RETURNING id, title
)
SELECT ins.id, sel.id AS from_id
FROM ins
JOIN sel USING (title);
If title
is not unique per query (but at least id
is unique per table):
WITH sel AS (
SELECT id, title, row_number() OVER (ORDER BY id) AS rn
FROM posts
WHERE id IN (1,2) -- select rows to copy
ORDER BY id
)
, ins AS (
INSERT INTO posts (title)
SELECT title FROM sel ORDER BY id -- ORDER redundant to be sure
RETURNING id
)
SELECT i.id, s.id AS from_id
FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN sel s USING (rn);
This second query relies on the undocumented implementation detail that rows are inserted in the order provided. It works in all current versions of Postgres and is probably not going to break.
db<>fiddle here
Old sqlfiddle
Retrieve original and new identities mapping from SELECT INSERT statement using OUTPUT clause
It can be achieved using MERGE INTO
and OUTPUT
:
MERGE INTO MyTable AS tgt
USING MyTable AS src ON 1=0 --Never match
WHEN NOT MATCHED THEN
INSERT (Name)
VALUES (src.Name)
OUTPUT
src.Id,
inserted.Id
INTO @idsMap;
Insert into ... values ( SELECT ... FROM ... )
Try:
INSERT INTO table1 ( column1 )
SELECT col1
FROM table2
This is standard ANSI SQL and should work on any DBMS
It definitely works for:
- Oracle
- MS SQL Server
- MySQL
- Postgres
- SQLite v3
- Teradata
- DB2
- Sybase
- Vertica
- HSQLDB
- H2
- AWS RedShift
- SAP HANA
- Google Spanner
Postgresql Insert select with multiple rows
To map them you have to join not on true, but on distinct row.
here is example how to join both on id with ordinality
- hope it will help. based on your json sample
t=# with j as (select '{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}'::json d)
select car,brand,t1.id from j
join json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on true
join json_array_elements_text(j.d->'brands') with ordinality t2(brand,id) on t1.id = t2.id
;
car | brand | id
-------------------+-------+----
bmw X5 xdrive | bmw | 1
volvo v90 rdesign | volvo | 2
(2 rows)
update elaborating for OP:
you can avoid mapping multiple rows, by aggregating em and then using index:
your fn:
create or replace function test_func(d json)
returns void as $$
begin
with j as (select d)
, a as (
select car,brand,t1.id oid
from j
join json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on true
join json_array_elements_text(j.d->'brands') with ordinality t2(brand,id) on t1.id = t2.id
)
, n as (
insert into t1 (name) values (d::json -> 'name') returning id
), c as (
insert into t2 (cars) select car from a order by oid returning id
)
, ag as (
select array_agg(c.id) cid from c
)
insert into t3 (id, name_id, cars_id, brand)
select 1, n.id,cid[oid], brand
from a
join n on true
join ag on true
;
end;
$$ language plpgsql;
your tables:
CREATE TABLE t1 ( "id" SERIAL PRIMARY KEY, "name" text NOT NULL );
CREATE TABLE t2 ( "id" SERIAL PRIMARY KEY, "cars" text NOT NULL );
CREATE TABLE t3 ( "id" int, "name_id" int REFERENCES t1(id), "cars_id" int REFERENCES t2(id), "brand" text );
execution:
t=# select test_func('{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}');
test_func
-----------
(1 row)
t=# select * from t1;
id | name
----+--------
14 | "john"
(1 row)
t=# select * from t2;
id | cars
----+-------------------
27 | bmw X5 xdrive
28 | volvo v90 rdesign
(2 rows)
t=# select * from t3;
id | name_id | cars_id | brand
----+---------+---------+-------
1 | 14 | 27 | bmw
1 | 14 | 28 | volvo
(2 rows)
Insert with ID from previous insert
In SQL Server, the safest way to do this is to use the output
clause, which is explained here:
declare @ids table (id int);
insert into A (Name)
output inserted.id into @ids
values (@name);
insert into AB (A_Id, B_Id)
select i.id, @b_Id
from @ids i;
Why is output
safer? First, it does not rely on transaction or session semantics. It simply captures the data that is actually being inserted and makes it available to subsequent statements.
Second, it can handle multiple rows being inserted (although that is not an issue in this case).
Third, it can return other columns apart from the ids, which can be handy.
SQL Select or Insert return ID
You should take care about transactions as well:
set XACT_ABORT on
begin tran
declare @ID int
select @ID = ID from names with (holdlock, updlock) WHERE DisplayName='chuck'
if @@rowcount = 0
begin
INSERT INTO names(DisplayName) values('chuck');
set @ID = scope_identity();
end
select @ID as ReturnID;
commit tran
Note the usage of table hints - holdlock and updlock. They prevent another thread from executing exactly the same query and creating the row a second time. For more information look for isolation, synchronization, deadlocks, concurrent updates.
Insert into with output clause
If table 1 and table 2 have a 1:1 relationship, and no foreign key exists between the two then you could do this in a single statement:
MERGE Table1 AS a
USING
( SELECT A.[group], A.account, B.title, B.amount, B.id2
FROM Table1 AS A
LEFT OUTER JOIN Table2 AS B
ON A.id = B.id2
) AS b
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ([group], account)
VALUES (b.[group], b.account)
OUTPUT inserted.Id, B.title, B.amount
INTO Table2(id2, title, amount);
Example on SQL Fiddle
Realistically though, if your tables are related they should have a foreign key, and in most cases they won't be 1:1, rather 1:n.
In which case you would still need to use MERGE
to caputre both the new ID and the old ID, but you would then need to capture this mapping in a temporary table before performing a second insert to Table2:
DECLARE @Map TABLE (OldID INT NOT NULL, NewID INT NOT NULL);
MERGE Table1 AS a
USING
( SELECT A.ID, A.[group], A.account
FROM Table1 AS A
) AS b
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT ([group], account)
VALUES (b.[group], b.account)
OUTPUT inserted.Id, b.ID
INTO @Map(NewID, OldID);
INSERT Table2 (id2, title, amount)
SELECT m.NewID, b.title, b.amount
FROM @Map AS m
INNER JOIN Table2 AS b
ON b.ID2 = m.OldID;
Example on SQL Fiddle
Related Topics
Equals(=) VS. Like for Date Data Type
Reuse Identity Value After Deleting Rows
SQL - Columns for Different Categories
How to Get the Raw Query String from Laravel's Query Builder Before Executing the Query
Why Isn't Row Level Security Enabled for Postgres Views
How to Select Columns from a Table Which Have Non Null Values
Delphi - Prevent Against SQL Injection
Windowed Functions Can Only Appear in the Select or Order by Clauses
How to Pull a List of Id's from a SQL Table as a Comma-Separated Values String
Ole Db Provider 'Microsoft.Jet.Oledb.4.0' Cannot Be Used for Distributed Queries
Sql: Delete All the Data from All Available Tables