artistdedigital (2/20/2014)
Merge means I want to get all the 6 columns(3 on each table) in to a new table...
Select R1,R2,R3
From Database.table1 as table.x
Select C1,C2,C3
From Database.table2 as table.y
It may surprise you, but tables are not just set of columns...
Your select will or may return some rows!
What about if select from your table Database.table1 returns as twice as many rows as select from Database.table2?
That do you execpt to be result of the merge? That is why I have asked you to provide the example of expected results based on a sample of data you have. Until you do that, you are no going to get relevant help, as it's imposible to see what is inside of your head (I've lost my crystal ball to do so)...
The maximum what I can tell you right now, that to have resultset containig 6 columns from the both selects you have you need to JOIN your table somehow. If there is nothing links rows of these two tables, then you have two options: 1. Cartesian product and 2. UNION ALL.
You already been shown examples of option #1:
To do union in your case you can try this:
INSERT [YourNewTable]
SELECT R1,R2,R3, NULL, NULL, NULL
FROM Database.table1 as table.x
UNION ALL
Select NULL, NULL, NULL, C1, C2, C3
FROM Database.table2 as table.y