INSERT INTO from multiple sources/tables

Question

I have a table FinalTable with these columns:

name, lastName, pesel, position, id_operator

I want to fill my FinalTable with values from 2 other tables:

AAA - this table has columns like name, lastName, pesel, position
BBB - this table has columns name, id_operator, pesel

I want to join AAA and BBB on the pesel column

insert into FinalTable (name, lastName, pesel, position, id_operator)
    select 
        name, lastName, pesel, position, 
        (select id_operator from BBB b where b.pesel = a.pesel) 
    from 
        AAA a;

How to do that? I'd like to set my last column id_operator to the value from BBB. The SQL query above is incorrect.

not sure if you're looking for join between two tables, or union all to have multiple selects. but try which ever fits your needs. EDIT: nvm, this is what you're looking for. http://www.postgresqltutorial.com/postgresql-inner-join/ — Hagai Wild, Feb 17 '19 at 16:54

Mureinik · Accepted Answer · 2019-02-17T17:09:00.733

2

I'd insert a join query:

INSERT INTO FinalTable  (name, lastName, pesel, position, id_operator)
SELECT a.name, a.lastName, a.pesel, a.position, b.id_operator
FROM   AAA a
JOIN   BBB b ON pesel = a.pesel;

edited Feb 17 '19 at 17:09

answered Feb 17 '19 at 16:56

Mureinik

297,002
52
306
350

I get the error: column reference name is ambiguous. Table BBB has 'name' column too. – Matley Feb 17 '19 at 17:08
2

@Matley you could fully qualify all the columns. See my edited answer. – Mureinik Feb 17 '19 at 17:09

score 2 · Answer 2 · answered Feb 17 '19 at 16:56

2

insert into FinalTable  (name, lastName, pesel, position, id_operator)
select name, lastName, pesel, position, id_operator from AAA a join BBB b on a.pesel=b.pesel;

answered Feb 17 '19 at 16:56

Vijiy

1,187
6
21

score 1 · Answer 3 · answered Feb 17 '19 at 16:56

1

use join between two tables

insert into FinalTable  (name, lastName, pesel, position, id_operator)
select name, lastName, pesel, position, id_operator    
from AAA a join BBB b on b.pesel = a.pesel

answered Feb 17 '19 at 16:56

Zaynul Abadin Tuhin

31,407
5
33
63

score 1 · Answer 4 · answered Feb 17 '19 at 17:11

1

You could use a inner join

insert into FinalTable  (name, lastName, pesel, position, id_operator)
select  a.name, a.lastName, a.pesel, a.position, b.id_operator 
from AAA a
INNER JOIN BBB b ON  b.pesel = a.pesel 
;

answered Feb 17 '19 at 17:11

ScaisEdge

131,976
10
91
107

INSERT INTO from multiple sources/tables

4 Answers4