Copy one table into another fails - "Column names in each table must be unique"

Question

When I try this:

SELECT * 
-- INTO DB2.dbo.CustomerOrderLines
FROM DB1.dbo.CustomerOrderLines 
INNER JOIN DB1.dbo.CustomerOrders ON DB1.dbo.CustomerOrders.Order_Display_Ref = DB1.dbo.CustomerOrderLines.Order_Display_Ref
WHERE DB1.dbo.CustomerOrders.Delivered_Date BETWEEN '2009-09-23' and '2009-09-24'

it show the rows correctly.

When I try to copy the contents from one table in DB1 into the same table in DB2 (and create it if it does not exist):

SELECT * 
INTO DB2.dbo.CustomerOrderLines
FROM DB1.dbo.CustomerOrderLines 
INNER JOIN DB1.dbo.CustomerOrders ON DB1.dbo.CustomerOrders.Order_Display_Ref = DB1.dbo.CustomerOrderLines.Order_Display_Ref
WHERE DB1.dbo.CustomerOrders.Delivered_Date BETWEEN '2009-09-23' and '2009-09-24'

it fails with

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

SELECT * INTO and INSERT INTO SELECT * work fine when copying other tables from one database into another, but they do not use JOINS.

What is my mistake?

Answer 1

It seems that CustomerOrderLines and CustomerOrders have one or more columns where the name is the same, e.g. CustomerOrderLines.Id and CustomerOrders.Id would cause this kind of clash as your query would attempt to create two columns both called Id.

note : at least one of your problems is coming from the column Order_Display_Ref, which appears in both these tables. There may be more.

Answer 2

Both tables on DB1 have the Order_Display_Refcolumn, so it's trying to insert it twice. Simply supply a column list rather than * and it should work fine.

Answer 3

You are joining DB1 table to itself and selecting all resulting columns. This will mean all column names are duplicated.

You need to specify the source you are inserting from:

SELECT DB1a.* 
INTO DB2.dbo.CustomerOrderLines
FROM DB1.dbo.CustomerOrderLines DB1a 
INNER JOIN DB1.dbo.CustomerOrders DB1b
  ON DB1.dbo.CustomerOrders.Order_Display_Ref = DB1.dbo.CustomerOrderLines.Order_Display_Ref
WHERE DB1.dbo.CustomerOrders.Delivered_Date BETWEEN '2009-09-23' and '2009-09-24'

Answer 4

You are joining two tables together which both have this Order_Display_Ref column in it. It looks like you only want the data from CustomerOrderLines so I would do :

SELECT DB1.dbo.CustomerOrderLines.* 
INTO DB2.dbo.CustomerOrderLines
FROM DB1.dbo.CustomerOrderLines 
INNER JOIN DB1.dbo.CustomerOrders ON DB1.dbo.CustomerOrders.Order_Display_Ref = DB1.dbo.CustomerOrderLines.Order_Display_Ref
WHERE DB1.dbo.CustomerOrders.Delivered_Date BETWEEN '2009-09-23' and '2009-09-24'

Answer 5

You want to add the content of the DB1.dbo.CustomerOrderLines to DB2.dbo.CustomerOrderLines?

Then, tell SQL Server in your SELECT that you want only the content of this table.

SELECT DB1.dbo.CustomerOrderLines.* 
INTO DB2.dbo.CustomerOrderLines
FROM DB1.dbo.CustomerOrderLines 
INNER JOIN DB1.dbo.CustomerOrders ON DB1.dbo.CustomerOrders.Order_Display_Ref = DB1.dbo.CustomerOrderLines.Order_Display_Ref
WHERE DB1.dbo.CustomerOrders.Delivered_Date BETWEEN '2009-09-23' and '2009-09-24'

Answer 6

Hi Andrei,

You might want to give a try to Volpet's Table Diff:

http://www.volpet.com/

You can try a fully-functional copy for 30 days.

Please let me know for anything.

Thank you,

Giammarco