SQLTeam.com | Weblogs | Forums

Temporary Table - column names must be unique

tsql
sql2008r2

#1

I am getting the following error message:

Column names in each table must be unique. Column name 'ColumnName' in table '#tmpTbl' is specified more than once.

I know it has to do with the **join **and yes, I know I can also declare a table with all the parameters but there are 30 separate tables I need to do this with and that's time consuming.

Here's the script:

SELECT * INTO #tmpTbl
FROM Archive.dbo.ShipTo i
INNER JOIN Archive.dbo.Order o ON o.ShipToID = i.ShipToID
WHERE o.OrderID = 15051
ALTER TABLE #tmpTbl
DROP COLUMN ArchiveDate
select * from #tmpTbl

I've tried a few things and searched around for a solution but just can't find one.
And like I mentioned, creating a table like DECLARE @tmp TABLE (Col1 INT, ...) would be time consuming.

Thanks for any input.


#2

And of course, five minutes after posting this, I figure it out.
It should be:

SELECT i.* INTO #tmpTbl


#3

IN general, don't use SELECT * or SELECT i.* as in your case, at least not in production code. It's OK for debugging


#4

Well this script will only be used very rarely to move an item from the archives back to production.
Otherwise I would and always do list the column names.