Query / Cursor in This case? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query / Cursor in This case?

Author

Topic

NgKH
Starting Member

15 Posts

Posted - 2003-03-06 : 10:21:28

I have to do a comparsion to various columns in 2 tables with the same table structure, for example

Table Person1 with the following structure
FirstName
LastName
PhoneNumber
Address
City
PostalCode
Birthdate
PersonID

and Table Person2 with the exact same structure

I would have to compare all the records between table Person1 and Person2 with the same person ID.

In this case should cusor be the appropiate choice or would writing queries comparing different fields is better in terms of performance. # of rows will be close to 1 million records per table

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 10:33:26

NO CURSORS!!!!!

SELECT A.PersonID,
CASE A.FirstName WHEN B.FirstName THEN 'Matches' ELSE 'Different' END AS FirstName,
CASE A.LastName WHEN B.LastName THEN 'Matches' ELSE 'Different' END AS LastName,
CASE A.PhoneNumber WHEN B.PhoneNumber THEN 'Matches' ELSE 'Different' END AS PhoneNumber,
CASE A.Address WHEN B.Address THEN 'Matches' ELSE 'Different' END AS Address,
CASE A.City WHEN B.City THEN 'Matches' ELSE 'Different' END AS City,
CASE A.PostalCode WHEN B.PostalCode THEN 'Matches' ELSE 'Different' END AS PostalCode,
CASE A.BirthDate WHEN B.BirthDate THEN 'Matches' ELSE 'Different' END AS BirthDate
FROM Person1 A INNER JOIN Person2 B ON A.PersonID=B.PersonID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-06 : 11:48:20

I like doing my compares with group bys .....

That way if records exist in 1 table but not the other you are all set. Rob's will not show you missing records in either table.

I have a stored proc in the "Scripts" section that does this for you easily .

SELECT MIN(TableName) as Table, PersonID, FirstName, LastName, PhoneNumber, ...
FROM
(SELECT 'Table1' AS TableName, PersonID, Firstname, LastName, ...etc...
FROM TAble1
UNION ALL
SELECT 'Table2' as TableName, PersonID, Firstname, lastname, etc....
FROM Table2
) A
GROUP BY PersonID, FirstName, LastName, PhoneNumber, ..etc...
HAVING COUNT(*) = 1

That returns all records in either table that are diffrent . Doesn't tell you the exact field, however.. which Rob's does.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources