My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. However, when you need to return one set only, then by all means use the NOT EXISTS technique, because the UNION ALL won't do the job in a single set compare. PC_ID; I get this: (No Table Names) Query9 PC_ID Client Name ID Client AFE 66 14 66 15 Then If I do this: (Like you have it I think) SELECT (Table Name) as Table Name, tmp. I state it like that because you "could" have a 'false positive' scenario or even possibly a mild cartesian (unique indexes can have NULL columns - not everyone uses proper primary keys! As far as I am concerned, even though the NOT EXISTS method is still set-based, the UNION ALL is certainly more pure and less likely to give a false sense of comparison. FROM A UNION ALL SELECT 'Table B' as Table Name, COUNT(*) NDUPS, B. So I am comparing two tables in different DBs, do I just put in the conventional table name without any extensions? I have to tables (A and B) and want only see lines that are in A which are different or missing from BIs this possible and if so how?
However, this means that the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want. If any columns are NULL, the WHERE condition will fail (even if the rows both match perfectly in both tables) 3) The UNION technique also returns all unmatching rows from both tables, so you can see why any rows don't match. Yours finished in just over 8 minutes, mine was done in less than 7 minutes. COLUMNS A WHERE table_name = @table A UNION ALL SELECT @Table B as Table Name, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME FROM INFORMATION_SCHEMA. The point of this technique is to see rows in BOTH tables are different.
We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table. The the technique you have presented only lists out rows on 1 table that have no match. However, I would not right a UNION ALL at all because that where it slows down! COL2,'*') ...) UNION ALL SELECT Table B' AS Table_Name, ID, COL1, COL2, COL3, COL4 FROM B WHERE NOT EXISTS (SELECT 1 FROM A WHERE B. COLUMNS B WHERE table_name = @table B ) AS tmp GROUP BY COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME HAVING COUNT(*) = 1 ORDER BY COLUMN_NAME, ORDINAL_POSITION I keep getting rows from both tables. If I change one value in one row of one table and run the query I get the changed row and the corsponding row from the other table. If you want to only return rows a particular table, then you can either do a LEFT OUTER JOIN or apply a filter to this technique (Having COUNT(*)=1 and MIN(Table Name)='Table To Show') If that doesn't help you out, I'll need a specific example of what you are trying to do. I can’t get the name of the table to show up correctly.
However, performance gains depend on having correct indexes, joins, and other considerations in place. Purchase Date WHEN NOT MATCHED BY TARGET THEN INSERT (Customer ID, Product ID, Last Purchase Date) VALUES (Source. That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. When the source and target are of similar size and the index guidelines described previously in the 'Index Best Practices' section are applied to the source and target tables, a merge join operator is the most efficient query plan.
This topic provides best practice recommendations to help you achieve optimal performance when using the MERGE statement. This is because both tables are scanned once and there is no need to sort the data. Order Qty If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally.