SQL INSERT INTO来自多个表

this is my table 1:

这是我的表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:

这是我的表2：

NUMBER       ID
555-1111     1
555-2222     2
555-3333     3

and now I want a table 3 which shows me all information:

现在我想要一张表3，它向我展示了所有信息：

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.

我首先尝试只将表1中的值插入表3，然后我将表2中的值插入到表3中，其中内部连接的位置为Id = Id。

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.

但我得到的只是重复我的价值观。而不是有3个条目，我有9个条目，其中一些数字为空，一些只有数字，其余为空，有些是正确的。

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?

我怎么能将所有3个表合并到一个表中？

7 个解决方案

#1

You only need one INSERT:

你只需要一个INSERT：

INSERT INTO table3 ( 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

#2

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:

我建议你不要创建一个新表，只需使用一个结合了两个表的视图，这样如果表1或表2中的任何数据发生变化，你就不需要更新第三个表了：

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:

如果你知道所有记录都在表1中，而只有表2中的一些记录，那么你应该使用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

如果您知道所有记录都在表2中，并且只有表2中的某些记录，那么您可以使用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):

或者只是颠倒表的顺序并使用LEFT JOIN（我发现这比正确的连接更合乎逻辑，但这是个人偏好）：

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;

#3

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.

通过使用LEFT JOIN，这将插入表3中表1的每个记录，对于与table2中的连接条件匹配的记录，它也将插入它们的编号。

#4

If I'm understanding you correctly, you should be able to do this in one query, joining table1 and table2 together:

如果我理解正确，您应该能够在一个查询中执行此操作，将table1和table2连接在一起：

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

#5

Here is an short extension for 3 or more tables to the answer of D Stanley:

以下是3个或更多表的简短扩展，以回答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

#6

To show the values from 2 tables in a pre-defined way, use a VIEW

要以预定义的方式显示2个表中的值，请使用VIEW

http://www.w3schools.com/sql/sql_view.asp

#7

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:

下面是一个示例，如果多个表没有公共ID，您可以自己创建，我使用1作为commonId来创建公共ID，以便我可以内部加入它们：

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

#1