CODEWITHSUNDEEP
sql-server
Andrei Drynov
Andrei Drynov

Reputation: 8592

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

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?

Upvotes: 2

Views: 20732

Answers (6)

BelGen
BelGen

Reputation: 1

Or simply rename the column(s) having the same name.

SELECT

TableA.Column1,
TableA.Column1,
TableA.Column2,
TableA.Order_Display_Ref,
TableB.Order_Display_Ref as TableBOrder_Display_Ref,
TableB.ColumnN

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'

Upvotes: 0

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11357

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'

Upvotes: 5

Mongus Pong
Mongus Pong

Reputation: 11477

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'

Upvotes: 2

Oded
Oded

Reputation: 499052

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'

Upvotes: 1

Doogie
Doogie

Reputation: 815

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.

Upvotes: 1

ZombieSheep
ZombieSheep

Reputation: 29953

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.

Upvotes: 1

Related Questions