Sql-server – Move/Copy unique rows from TableA to TableB, if they are not in TableB

exceptselectsql serversql-server-2005

I'm required to move every unique row from TableA to TableB, if the unique row from TableA is not already in TableB.

Both tables look the same, they don't have any keys, but there's a combination of columns that make their rows unique for my task – num which is an int and com, a varbinary(512).

As my SQL is horrible I wanted to create a script that does the job, but I have ridiculous problems, escaping/handling the varbinary value. This is why I'm asking for help.

I can pseudo-code..

moving/copying::

SELECT * INTO TableB
WHERE (unique TableA.num + TableA.com)
AND NOT IN (TableB)
FROM TableA

afterwards, for moving only

DELETE FROM TableA WHERE (unique from above)

(Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Additional details

Sorry. I'm still learning SQL AND asking proper questions..

TableA & TableB have the same design. They have multiple columns, including the two above mentioned ones. I'm required to both move and copy (depending on the chosen option). TableA is the source, TableB is the destination. Both tables may contain numerous occurrences of the same int/com combination. I need to have only one (it doesn't matter which one) of each of the combinations. With copying they're not removed from TableA, when moving, they should be.

Best Answer

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB.

You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB.

SELECT
  int,
  com
FROM TableA
GROUP BY int, com
HAVING COUNT(*) = 1

EXCEPT 

SELECT
  int,
  com
FROM TableB

If you actually wanted to SELECT ALL distinct records from TableA (regardless of how many times they occur in TableA) that do not already appear in TableB, then you could use the DISTINCT keyword.

Edit: As @ypercube correctly pointed out, EXCEPT already filters for DISTINCT rows

SELECT 
  int,
  com
FROM TableA

EXCEPT 

SELECT
  int,
  com
FROM TableB

If the two tables have more columns, it's handier to use NOT EXISTS instead of EXCEPT. You could also use ROW_NUMBER() to select an arbitrary row in 2005+ versions. So, the copying from table A to B would be:

INSERT INTO TableB
  (int, com, --- rest of the columns)
SELECT
  int, com, --- rest of the columns,
FROM
  ( SELECT
      int, com, --- rest of the columns,
      rn = ROW_NUMBER() OVER (PARTITION BY int, com ORDER BY someColumn)
    FROM 
      TableA AS a
    WHERE
      NOT EXISTS
      ( SELECT *
        FROM TableB AS b
        WHERE b.int = a.int
          AND b.com = a.com
      ) 
  ) AS copy
WHERE 
    rn = 1 ;
Related Question