SQL INSERT INTO from multiple tables

Question

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

User · Answer

I would suggest instead of creating a new table, you just use a view that combines the two tables, this way if any of the data in table 1 or table 2 changes, you don't need to update the third table:

CREATE VIEW dbo.YourView
AS
    SELECT  t1.Name, t1.Age, t1.Sex, t1.City, t1.ID, t2.Number
    FROM    Table1 t1
            INNER JOIN Table2 t2
                ON t1.ID = t2.ID;

If you could have records in one table, and not in the other, then you would need to use a full join:

CREATE VIEW dbo.YourView
AS
    SELECT  t1.Name, t1.Age, t1.Sex, t1.City, ID = ISNULL(t1.ID, t2.ID), t2.Number
    FROM    Table1 t1
            FULL JOIN Table2 t2
                ON t1.ID = t2.ID;

If you know all records will be in table 1 and only some in table 2, then you should use a LEFT JOIN:

CREATE VIEW dbo.YourView
AS
    SELECT  t1.Name, t1.Age, t1.Sex, t1.City, t1.ID, t2.Number
    FROM    Table1 t1
            LEFT JOIN Table2 t2
                ON t1.ID = t2.ID;

If you know all records will be in table 2 and only some in table 2 then you could use a RIGHT JOIN

CREATE VIEW dbo.YourView
AS
    SELECT  t1.Name, t1.Age, t1.Sex, t1.City, t2.ID, t2.Number
    FROM    Table1 t1
            RIGHT JOIN Table2 t2
                ON t1.ID = t2.ID;

Or just reverse the order of the tables and use a LEFT JOIN (I find this more logical than a right join but it is personal preference):

CREATE VIEW dbo.YourView
AS
    SELECT  t1.Name, t1.Age, t1.Sex, t1.City, t2.ID, t2.Number
    FROM    Table2 t2
            LEFT JOIN Table1 t1
                ON t1.ID = t2.ID;

User · Answer

Here is an short extension for 3 or more tables to the answer of D Stanley   INSERT INTO other table  name  age  sex  city  id  number  nationality  SELECT name  age  sex  city  p id  number  n nationality FROM table 1 p INNER JOIN table 2 a ON a id   p id INNER JOIN table 3 b ON b id   p id     INNER JOIN table n x ON x id   p id

User · Answer

To show the values from 2 tables in a pre-defined way  use a VIEW  http   www w3schools com sql sql view asp

User · Answer

Try doing   INSERT INTO table3 NAME AGE SEX CITY ID NUMBER  SELECT t1 name t1 age  t1 sex t1 city t1 id t2 number FROM table1 t1 LEFT JOIN table2 t2 ON t1 id   t2 id   By using LEFT JOIN  this will insert every record from table 1 in table3  and for the ones that match the join condition in table2  it will also insert their number

User · Answer

Here is an example if multiple tables don t have common Id  you can create yourself  I use 1 as commonId to create common id so that I can inner join them   Insert Into  TempResult select CountA  CountB  CountC  from    select Count A Id  as CountA  1 as commonId from tableA   where        and        and         as tempA  inner join   select Count B Id  as CountB  1 as commonId from tableB   where       and       and         as tempB  on tempA commonId   tempB commonId  inner join   select Count C ID  as CountC  1 as commonId from tableC where     and         as tempC  on tmepB commonId   tempC commonId  --view insert result select   from  TempResult

User · Answer

If I m understanding you correctly  you should be able to do this in one query  joining table1 and table2 together   INSERT INTO table3   name  age  sex  city  id  number  SELECT p name  p age  p sex  p city  p id  c number FROM table1 p INNER JOIN table2 c ON c Id   p Id

User · Answer

You only need one INSERT   INSERT INTO table4   name  age  sex  city  id  number  nationality  SELECT name  age  sex  city  p id  number  n nationality FROM table1 p INNER JOIN table2 c ON c Id   p Id INNER JOIN table3 n ON p Id   n Id

[sql] SQL INSERT INTO from multiple tables

Examples related to sql

Examples related to sql-server