SQL INSERT INTO from multiple tables
sql, sql-server
Solution
You only need one INSERT:
INSERT INTO destinationTable( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, s1.id, number, s3.nationality
FROM sourceTable1 s1
INNER JOIN sourceTable2 s2 ON s2.Id = s1.Id
INNER JOIN sourceTable3 s3 ON s3.Id = s1.Id
Problem
this is my table 1: ``` NAME AGE SEX CITY ID Clara 22 f New York 1 Bob 33 m Washington 2 Sam 25 m Boston 3 ``` this is my table 2: ``` NUMBER ID 555-1111 1 555-2222 2 555-3333 3 ``` and now I want a table 3 which shows me all information: ``` NAME AGE SEX CITY ID NUMBER Clara 22 f New York 1 555-1111 Bob 33 m Washington 2 555-2222 Sam 25 m Boston 3 555-3333 ``` I tried first to insert into table 3 only the values from table 1 and then I inserted into table 3 the values from table 2 with an inner join where Id = Id is. ``` INSERT INTO table3 { name, age, sex, city, id} SELECT name, age, sex, city, id FROM table 1 INSERT INTO table3 { name, age, sex, city, id, number} SELECT name, age, sex, city, id, number FROM table 2 p INNER JOIN table 3 c ON c.Id = p.Id ``` But all I get is a duplication of my values. instead of having 3 entries, I have like 9 entries, which some have number null, some have only the number and the rest null, and some are correct. I hope someone can help me EDIT If I am having now a third Table like this one: ``` NATIONALITY ID Canadian 1 American 2 French 3 ``` How could I merge all 3 tables into one Table?