Insert into not working

, , ,
1

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
2

What does it do? Does it give you an error message, or does it silently fail without giving any error messages, or is it something else?

If the table named Differences already exists, this will fail. In that case, you should use INSERT INTO Differences SELECT ....

3

It shows syntax error at the very last parenthesis. Thanks.

4

try removing last ) and add alias.


  ) Y
  ON X.[Pub Eff] = Y.[ID]     
  )t2
5

That is exactly what I did and it now says "The column 'RowNumber' was specified multiple times for 'z'" Thanks..

6

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.

7

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.

8

James,
Got rid of the first which and it worked. Thank you for your time, you have saved my life couple of times before as well. Cannot thank enough.