Search code examples
sqlsql-serverselectselect-into

Select into Table from Table2 where column in (Subquery)

SELECT .... ColumnNames ...
INTO [FOUND_DUPLICATES] 
FROM [FIND_DUPLICATES] AS FD
WHERE FD.[Contract No] IN 
    (SELECT [Contract No],
            [Vehicle Identity number (VIN)],
            COUNT(*) AS Anzahl 
    FROM FIND_DUPLICATES
    GROUP BY 
            [Contract No],
            [Vehicle Identity number (VIN)]
    HAVING COUNT(*) >1)

Here u can see what I want :)

Find duplicates and copy them to another table. But with this code I get an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The SELECT statement to find the duplicates is working very well. But I have a problem to select and copy only the duplicates to the new table with Select Into.

I hope u can understand what I want and anyone can help me with that :)

//EDIT: Im using SQL Server 2008 R2

Solution

  • Adding another sub query should work?

    Select  .... ColumnNames ...

INTO [FOUND_DUPLICATES] 
    FROM [FIND_DUPLICATES]
    AS FD
    where FD.[Contract No] 
       IN (  Select [Contract No] from (Select 
        [Contract No],
        [Vehicle Identity number (VIN)],
        COUNT(*) AS Anzahl 
    from FIND_DUPLICATES
    group by 
        [Contract No],
        [Vehicle Identity number (VIN)]
    having count(*) >1)x)