join two tables into one big table

Question

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

Answer 1

May it work to just do:

SELECT col1, col2, col3 INTO Table1 FROM Table2

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

Answer 3

I think the best option is to create a view in sql server, this will optimize the performance of the query:

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.

Answer 4

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

Answer 5

This really helped me alot.

Thaks you somuch.