Обсуждение: Elegant way to insert of some value in some tables in shortinstruction
Hi all, i'm very novice in SQL and try to find an elegant way to insert some values to some tables (that i'll later query with jointure for have a view of only desired informations) but in short way. All manuals i saw until now told me like: insert into table1 values('value1','value2','value3'); insert into table2 values('value1','value2','value3'); Well, it's nice but doesn't it exists a way to make same in 1 operation? Thanks for your answer
Short version is when using insert with values you can put a bunch of new records into the SAME table like this... insert into table1 values (1, 'One'), (2, 'Two'), (3, 'Three'); ...but inserts into different tables need to be done in different statements. -----Original Message----- From: philolilou [mailto:philolilou@free.fr] Sent: Wednesday, March 13, 2019 3:04 PM To: pgsql-novice@postgresql.org Subject: Elegant way to insert of some value in some tables in short instruction Hi all, i'm very novice in SQL and try to find an elegant way to insert some values to some tables (that i'll later query with jointure for have a view of only desired informations) but in short way. All manuals i saw until now told me like: insert into table1 values('value1','value2','value3'); insert into table2 values('value1','value2','value3'); Well, it's nice but doesn't it exists a way to make same in 1 operation? Thanks for your answer
Re: Elegant way to insert of some value in some tables in shortinstruction
От
Andreas Kretschmer
Дата:
Am 13.03.19 um 20:25 schrieb David Raymond: > > ...but inserts into different tables need to be done in different statements. you can insert into 2 tables within 1 statement using writeable common table expressions (wCTE): test=# create table table1(a int, b int, c int); CREATE TABLE test=*# create table table2(a int, b int, c int); CREATE TABLE test=*# with x as (insert into table1 values (1,2,3) returning *) insert into table2 select * from x; INSERT 0 1 test=*# select * from table1; a | b | c ---+---+--- 1 | 2 | 3 (1 row) test=*# select * from table2; a | b | c ---+---+--- 1 | 2 | 3 (1 row) test=*# with x as (insert into table1 values (4,5,6) returning *) insert into table2 select * from x; INSERT 0 1 test=*# select * from table1; a | b | c ---+---+--- 1 | 2 | 3 4 | 5 | 6 (2 rows) test=*# select * from table2; a | b | c ---+---+--- 1 | 2 | 3 4 | 5 | 6 (2 rows) test=*# Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com