Combining two tables with multiple common columns into one in SQL

Question

I am trying to create a table in SQL server which has the same output as the following:

Select *  
FROM Table1  
LEFT JOIN Table2  
ON  
    Table1.Key1 = Table2.Key1 
AND Table1.Key2 = Table2.Key2

The result of the above query is exactly what I need, but as a new table.

The problem is, there are multiple columns that are common between the two tables. I have executed the following code:

Select *  
INTO NewTable  
FROM Table1  
LEFT JOIN Table2  
ON    
    Table1.Key1 = Table2.Key1 
AND Table1.Key2 = Table2.Key2

The following error appears:

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'Key1' in table 'NewTable' is specified more than once.

Could someone please help? I would highly appreciate it after a long day of searching the internet without any solution.

Thank you so much in advance!

Answer 1

This will help you identify what records you need to get a unique list.

select ',' + Column_Name
from INFORMATION_SCHEMA.COLUMNS c2
where column_Name not in (
                        select COLUMN_NAME
                        from INFORMATION_SCHEMA.COLUMNS
                        where TABLE_NAME = 'table1')
and table_Name = 'Table2'

So you can safely say:

Select table1.*
    <<Paste in your results from above here>>  
INTO NewTable  
FROM Table1  
LEFT JOIN Table2  
ON    
    Table1.Key1 = Table2.Key1 
AND Table1.Key2 = Table2.Key2