I was asked for a simple way to
compare two tables that should be identical and have the same schema. I
provided the following scripts to show records that are identical (Intersect) and different (Except):
IF OBJECT_ID(N'dbo.test1') IS NOT NULL
DROP TABLE test1
CREATE TABLE dbo.test1 (name VARCHAR(20), dob DATETIME)
IF OBJECT_ID(N'dbo.test2') IS NOT NULL
DROP TABLE test2
CREATE TABLE dbo.test2 (name VARCHAR(20), dob DATETIME)
INSERT INTO test1 (name, dob)
VALUES ('Fred','20010102')
INSERT INTO test1 (name, dob)
VALUES ('Fred1','20010103')
INSERT INTO test1 (name, dob)
VALUES ('Fred2','20010104')
INSERT INTO test1 (name, dob)
VALUES ('Fred3','20010105')
INSERT INTO test1 (name, dob)
VALUES ('Fred4','20010106')
INSERT INTO test1 (name, dob)
VALUES ('Fred5','20010107')
INSERT INTO test2 (name, dob)
VALUES ('Fred','20010102')
INSERT INTO test2 (name, dob)
VALUES ('Fred2','20010104')
INSERT INTO test2 (name, dob)
VALUES ('Fred3','20010105')
INSERT INTO test2 (name, dob)
VALUES ('Fred7','20010106')
INSERT INTO test2 (name, dob)
VALUES ('Fred5','20010107')
--exist and are the same in both tables test 1, not in test 2
SELECT name, dob
FROM dbo.test1
INTERSECT
SELECT name, dob
FROM dbo.test2
--exists in test 1, does not exist in test 2
SELECT name, dob
FROM dbo.test1
EXCEPT
SELECT name, dob
FROM dbo.test2
--exists in test 2, does not exist in test 1
SELECT name, dob
FROM dbo.test2
EXCEPT
SELECT name, dob
FROM dbo.test1
As you can see by running the script above, intersect shows
values from the first query where there are identical record matches in the
second query. Except shows values in the first query where there are No identical
record matches in the second query. Additional benefit is that it also includes
null values in the comparison, you do not get the same functionality with null
values on joins (when you just enter the column names) from 2 tables (without
adding specific null handling).
No comments:
Post a Comment