SQL 将两张桌子连接成一张大桌子
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/327366/
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
join two tables into one big table
提问by David Ackerman
I have two tables with the same columns, and I need to copy one table's rows to the other table's rows to create one big table with all the values from both tables. Right now I am doing this query to return the same thing:
我有两个具有相同列的表，我需要将一个表的行复制到另一个表的行以创建一个包含两个表中所有值的大表。现在我正在执行此查询以返回相同的内容：
SELECT col1, col2, col3 from Table1
union
SELECT col1, col2, col3 from Table2
However, it seems horribly inefficient, and on my system is very slow (returns 1210189 records).
然而，它似乎非常低效，并且在我的系统上非常慢（返回 1210189 条记录）。
回答by Tommy
May it work to just do:
可以这样做：
SELECT col1, col2, col3
INTO Table1
FROM Table2
回答by Dustin
Start with union all:
从 union all 开始：
select col1, col2, col3 from Table1
union all
select col1, col2, col3 from Table2
Your query is trying to deduplicate things, which would slow it down considerably.
您的查询正在尝试对事物进行重复数据删除，这会大大减慢它的速度。
回答by Hosam Aly
You could use this to fill the second table:
您可以使用它来填充第二个表：
Insert into table2 select * from table1;
Or if you want to be more specific:
或者，如果您想更具体：
Insert into table2(col1, col2, col3) select col1, col2, col3 from table1;
(Note: some DBMSs might require putting parenthesis around the SELECT clause.)
（注意：一些 DBMS 可能需要在 SELECT 子句周围加上括号。）
回答by netadictos
I think the best option is to create a view in sql server, this will optimize the performance of the query:
我认为最好的选择是在 sql server 中创建一个视图，这将优化查询的性能：
SELECT col1, col2, col3 from Table1
union all
SELECT col1, col2, col3 from Table2
(As other users said: "union" is used to select distinct values from two tables where as "union all" is used to select all values including duplicates from the tables.)
（正如其他用户所说：“联合”用于从两个表中选择不同的值，而“联合全部”用于从表中选择包括重复项在内的所有值。）
At the same time I would restrict the number of rows I get from the database if i am writing them for a web and if this is giving me problems, with the new functions of Sql Server 2005 row_number(), with this I would page results.
同时，如果我正在为 Web 编写它们并且这给我带来问题，我将限制从数据库中获得的行数，使用 Sql Server 2005 row_number()的新功能，我将使用这个页面结果.
回答by user1157685
select * into new table(your new table name)
from table1.col1,table1.col2,table2.col1;
here columns can be your required columns .
这里的列可以是您需要的列。
回答by Carl
select * into newtable from table1
union all
select * from table2
Worked well. Guidelines, both tables have exact same column names :)
工作得很好。准则，两个表具有完全相同的列名 :)
