将两个表合并到一个大表中

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条记录)。

5 个解决方案

#1

May it work to just do:

只要做:

SELECT col1, col2, col3 
INTO Table1
FROM Table2

#2

Start with 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.

您的查询正在尝试去复制东西，这将大大降低它的速度。

#3

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子句周围加上括号。)

#4

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.)

(正如其他用户所说:“union”用于从两个表中选择不同的值，其中“union all”用于从表中选择包括重复值在内的所有值。)

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()的新函数，我将对结果进行分页。

#5

select * into new table(your new table name) 
from table1.col1,table1.col2,table2.col1;

here columns can be your required columns .

这里的列可以是您所需要的列。

#1