Sql-server – Copy a SQL Server database from one server to another on demand

If you run the copy database wizard then yes all the data will go through which ever machine you run the copy database wizard on.

If you are moving a large database, especially one with a complex schema then backup and restore is a much better option.

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 ;