'Non-unique column' error when creating a third table using SELECT INTO and FULL JOIN I'm attempting to create a new table from two existing tables, using the following code:

SELECT * INTO CustAndOrders from SalesLT.Customer 
FULL JOIN 
SalesLT.SalesOrderHeader
ON Customer.CustomerID=SalesOrderHeader.CustomerID;


However, I'm getting the error:

Column names in each table must be unique. Column name 'CustomerID' in table 'CustAndOrders' is specified more than once.


My intention is to create a third table with all the fields from each of the joined tables. Obviously 'CustomerID' appears twice, that's what I am joining on. And if I remove the INTO part and execute as a SELECT statement, it works as expected.
Just in case it isn't clear what the problem is the result of the SELECT has two columns called CustomerID. One from each table. A table can't have two identically named columns so the attempt to create a table with that format fails.

The solution is don't use *. 

List out the columns you want explicitly and remove duplicate columns that you don't need or use column aliases to disambiguate any dupes that you do actually need.

Usually for a full outer join what you actually want is 

SELECT COALESCE(Customer.CustomerID,SalesOrderHeader.CustomerID) as  CustomerID
       /*, other columns*/  
INTO   CustAndOrders
FROM   SalesLT.Customer
       FULL JOIN SalesLT.SalesOrderHeader
              ON Customer.CustomerID = SalesOrderHeader.CustomerID; 


but it seems dubious that you need a FULL OUTER JOIN here anyway. Probably you need a LEFT JOIN