Comparing two Files - 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 Files

Author

Topic

bagzli
Starting Member

17 Posts

Posted - 2012-01-24 : 10:54:34

Hi, I was wondering if you guys can help me out. I have 2 different tables to compare. Table A and Table B.

Table A has records that Table B does not, when they should be the same. How can i tell table A to go through table B and get rid of the duplicates so only the records from Table A that are not in Table B are shown. I was thinking something with Union but that will give me all the records, is it possible to do it with a select and where cause or maybe with a join? If possible i'd prefer not to use Union

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-24 : 11:00:10

select * from tableA t1
where not exists(select * from tableB t2 where t2.KeyColumn = t1.KeyColumn)

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-24 : 11:08:40

Alternatively use EXCEPT


DECLARE @foo TABLE (
	[val] INT
	)
DECLARE @bar TABLE (
	[val] INT
	)
	
INSERT @foo ([val])
VALUES (1),(2)


INSERT @bar ([val])
VALUES (2)

       SELECT * FROM @foo
EXCEPT SELECT * FROM @bar

which can be used if there is no key column or if the rows may be different for the same keys.

NOT EXISTS may be faster. Not sure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bagzli
Starting Member

17 Posts

Posted - 2012-01-24 : 12:14:59

Thanks, I believe it worked. I'll need to check out the data later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources