Insert multiple rows with one column from another table

Question

I have two tables

 CREATE TABLE table1 (
    id bigint NOT NULL,
    name character varying(255),
    CONSTRAINT table1_pkey PRIMARY KEY (id)
 );

 CREATE TABLE table2 (   
     id bigint NOT NULL,
     name character varying(255),
     table1_id bigint,
     CONSTRAINT table2_pkey PRIMARY KEY (id), 
     CONSTRAINT fk_table1_table2 FOREIGN KEY (table1_id) 
     REFERENCES table1 (id) MATCH SIMPLE
 );

now what i want to do is for each entry in table1 add entry in table2

ie if my table 1 has entries

|id | name   |
|1  | First  | 
|2  | Second | 
|3  | Third  |

I need to create three entries in table2

insert into table2 (id,name,table2_id) values (nextval('table2_seq'),'new entry', 1);
insert into table2 (id,name,table2_id) values (nextval('table2_seq'),'new entry', 2);
insert into table2 (id,name,table2_id) values (nextval('table2_seq'),'new entry', 3);

and as only foreign key is changed for each new entry, i was wonder is it any possibility to automate this process. Is it possible to achieve with query, or should i look at procedures?

user330315 · Accepted Answer · 2016-01-19 11:28:35Z

5

Use an insert based on a select:

insert into table2 (id,name,table1_id)
select nextval('table2_seq'), 'new entry', t1.id
from table1;

answered Jan 19, 2016 at 11:28

user330315

Sign up to request clarification or add additional context in comments.

Comments

Hemmels · Accepted Answer · 2021-04-29 08:52:16Z

0

I struggled to get a "SELECT ... WHERE" in the subquery, likely not enough coffee, but finalised on the below syntax:

insert into table (col1, col2 ...)
select 'staticval1',
r.variable1 from reftable r where r.variable2 = 'Some search term'
...;

answered Apr 29, 2021 at 8:52

Hemmels

8922 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Insert multiple rows with one column from another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related