Comparing Two Tables - 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

General SQL Server Forums

New to SQL Server Programming

Comparing Two Tables

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-23 : 16:24:46

I did some research and found some good information on how to compare two tables in SQL. However, there are some errors in my code and I am not sure what is causing the query to error.

Can anyone help?

dbo.FaISIR11

LastName
FirstName
Address
City
State
Zip
StudentEmail
FatherLastName
FatherFirstName
MotherLastName
MotherFirstName
ParentEmail

********ORIGINATING TABLE**********

dbo.FaFAFSA11

LastName
FirstName
Address
City
State
Zip
StudentEmail
FatherLastName
FatherFInitial
MotherLastName
MotherFInitial
ParentEmail

***********CODE TO COMPARE TABLES*********
SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

**************MY CODE*********************

SELECT MIN(dbo.FaISIR11) as dbo.FaISIR11, ID, dbo.FaISIR11.LastName, dbo.FaISIR11.FirstName, dbo.FaISIR11.Address,
dbo.FaISIR11.City, dbo.FaISIR11.State, dbo.FaISIR11.StudentEmail, dbo.FaISIR11.FatherLastName, dbo.FaISIR11.FAtherFirstName,
dbo.FaISIR11.MotherLastName, dbo.FaISIR11.MotherFirstName, dbo.FaISIR11.Parentemail
FROM
(
SELECT 'dbo.FaISIR11' as dbo.FaISIR11, A.ID, A.dbo.FaISIR11.LastName, A.dbo.FaISIR11.FirstName, A.dbo.FaISIR11.Address,
A.dbo.FaISIR11.City, A.dbo.FaISIR11.State, A.dbo.FaISIR11.StudentEmail, A.dbo.FaISIR11.FatherLastName, A.dbo.FaISIR11.FAtherFirstName,
A.dbo.FaISIR11.MotherLastName, A.dbo.FaISIR11.MotherFirstName, A.dbo.FaISIR11.Parentemail
FROM A
UNION ALL
SELECT 'dbo.FaFAFSA11' as dbo.FaFAFSA11, B.ID, B.dbo.FaFAFSA11.LastName, B.dbo.FaFAFSA11.FirstName, B.dbo.FaFAFSA11.Address,
B.dbo.FaFAFSA11.City, B.dbo.FaFAFSA11.State, B.dbo.FaFAFSA11.Zip,B.dbo.FaFAFSA11.FatherLastname, B.dbo.FaFAFSA11,FatherFInitial
B.dbo.FaFAFSA11.MotherLastName, B.dbo.FaFAFSA11.MotherFInitial, B.dbo.FaFAFSA11.ParentEmail
FROM B
) tmp
GROUP BY ID, Last Name, First Name, Address, City, State, Zip, Student Email, Father Last Name, Father First Name, Mother Last Name,
Mother First Name, Parent email
HAVING COUNT(*) = 1
ORDER BY ID

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-24 : 02:57:36

SELECT * FROM dbo.FaISIR11
EXCEPT
SELCT * FROM dbo.FaFAFSA11

will give you the rows which are present in Upper table and missing in the second table(Interchange the tables in SELECT's and you can find the opposite ie. rows which are in FaFAFSA11 but not in FaISIR11).

Thanks
ROhit

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-24 : 11:31:16

What I really need to do is compare the data inside of each column from one table to the other. Then I need to write an update query that will import information from the FaISIR11 table into the proper columns in the FaFAFSA11 table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources