来自多个表的 SQL INSERT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20404682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL INSERT INTO from multiple tables
提问by Paparis
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.
但我得到的只是我的价值观的重复。我有 9 个条目，而不是 3 个条目，其中一些条目为空，有些只有数字，其余为空，有些是正确的。
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 个表合并为一个表？
回答by D Stanley
You only need one INSERT:
你只需要一个 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
回答by GarethD
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;
回答by kaba713
Here is an short extension for 3 or more tables to the answer of D Stanley:
以下是 D Stanley 答案的 3 个或更多表的简短扩展：
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
回答by Filipe Silva
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，这会将表 1 中的每条记录插入到表 3 中，对于匹配表 2 中的连接条件的记录，它还会插入它们的编号。
回答by Andrew
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
回答by Mike
To show the values from 2 tables in a pre-defined way, use a VIEW
要以预定义的方式显示 2 个表中的值，请使用 VIEW
回答by yu yang Jian
Here is an example if multiple tables don't have common Id, you can create yourself, I use 1 as commonIdto create common id so that I can inner join them:
这是一个示例，如果多个表没有公共 ID，您可以自己创建，我1 as 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
