The following query is not working when I have insert into at the top of the query, otherwise it works: How to make it work? Thanks.
SELECT * INTO Differences FROM
(SELECT case when Y.RowNumber IS NULL then ROW_NUMBER() OVER (
ORDER BY y.[ID]
) else ROW_NUMBER() OVER (
ORDER BY y.[ID]) end RowNumber
, Y.Which
, Y.*
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY [ID]
) RowNumber
, Which = 'New Value'
, *
FROM (
SELECT *
FROM dbo.table1
EXCEPT
SELECT *
FROM dbo.table2
) X
) X
inner join(
SELECT ROW_NUMBER() OVER (
ORDER BY [ID]
) RowNumber
, Which = 'Old Value'
, *
FROM (
SELECT *
FROM dbo.table2
EXCEPT
SELECT *
FROM dbo.table1
) Y
) Y
ON X.[ID] = Y.[ID]
) Z
When you do a SELECT INTO, that is going to create a new table. In a table, no two columns can have the same name. The error message says that the result of the select has more than one column named RowNumber. Change the query to something like shown below.
SELECT *
INTO Differences
FROM ( SELECT CASE WHEN Y.RowNumber IS NULL
THEN ROW_NUMBER() OVER ( ORDER BY y.[ID] )
ELSE ROW_NUMBER() OVER ( ORDER BY y.[ID] )
END RowNumber ,
Y.Which ,
Y.*
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [ID] ) RowNumberNEW , --<< NEW NAME
Which = 'New Value' ,
*
FROM ( SELECT *
FROM dbo.table1
EXCEPT
SELECT *
FROM dbo.table2
) X
) X
INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY [ID] ) RowNumberOLD , --<<< NEW NAME
Which = 'Old Value' ,
*
FROM ( SELECT *
FROM dbo.table2
EXCEPT
SELECT *
FROM dbo.table1
) Y
) Y ON X.[ID] = Y.[ID]
) Z
However that may not fix the problem if there are other similar cases where you have more than one column with the same name. Run the select portion alone and see where there are dups.
Thank you James, that worked but now it says "The column 'Which' was specified multiple times for 'z'"
so I changed Which1 = 'New Value' and it still has the same error. Thanks.