Elegant way to insert of some value in some tables in short instruction

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Elegant way to insert of some value in some tables in short instruction

philolilou
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


Reply | Threaded
Open this post in threaded view
|

RE: Elegant way to insert of some value in some tables in short instruction

David Raymond
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:[hidden email]]
Sent: Wednesday, March 13, 2019 3:04 PM
To: [hidden email]
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


Reply | Threaded
Open this post in threaded view
|

Re: Elegant way to insert of some value in some tables in short instruction

Andreas Kretschmer-3


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