I've two tables with similar posts, and I have to copy one table's rows towards the other table's rows to produce one large table with the values from both tables. Right now i'm carrying this out query to come back exactly the same factor:
SELECT col1, col2, col3 from Table1 union SELECT col1, col2, col3 from Table2
However, it appears horribly inefficient, as well as on my product is very slow (returns 1210189 records).
Begin with union all:
select col1, col2, col3 from Table1 union all select col1, col2, col3 from Table2
Your totally attempting to deduplicate things, which may slow it lower substantially.
May it try to simply do:
SELECT col1, col2, col3
You could utilize this to fill the 2nd table:
Insert into table2 select * from table1;
Or if you wish to become more specific:
Insert into table2(col1, col2, col3) select col1, col2, col3 from table1;
(Note: some DBMSs may need putting parenthesis round the Choose clause.)
I believe the best choice is to produce a view in sql server, this can optimize the performance from the query:
SELECT col1, col2, col3 from Table1 union all SELECT col1, col2, col3 from Table2
(As other customers stated: "union" can be used to choose distinct values from two tables while "union all" can be used to choose all values including replicates in the tables.)
Simultaneously I would restrict the amount of rows I achieve with a home database should i be writing them for any web and when this really is giving me problems, using the new functions of Sql Server 2005 row_number(), with this particular I'd page results.