Crazy Query
-
How do I go about taking two tables from two databases (with same column names) and make it look like one table? I need to do this with a query and not moving data from one db to another. HELP!!!!Database1
ID LastName FirstName 1 Lee Terry 2 Something Anything Database2
ID LastName FirstName 1 Smith Fran 2 Jackson Miguel Select Database1.LastName as LastName, Database1.FirstName as FirstName, Database2.LastName as LastName, Database2.FirstName as FirstName?????Final result
ID LastName FirstName 1 Smith Fran 2 Lee Terry 3 Jackson Miguel 4 Something Anything -
Use union.
select lastname, firstname from database1..tablename
union all
select lastname, firstname from database2..tablename
-
-
In case you're not aware. By default using Union (without the ALL) by itself will remove any duplicates in the results. Example: Cindy Jones resides in both of your tables (databases)
Using UNION by itself the result will show Cindy Jones once
BUT using UNION ALL will show ALL results:
Cindy Jones
Cindy Jones
-
Cindy is right - but should probably also have said UNION ALL is much quicker as SQL does not have to search for duplicates - so if you don't need the duplicates filtered out (or know there will be none) then go for UNION ALL
James Horsley
Workflow Consulting Limited
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply