Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Row-by-Row comparison Expand / Collapse
Author
Message
Posted Friday, June 05, 2009 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:58 AM
Points: 28, Visits: 45
I have two tables, stg,main with the same structure.Each day data is refeshed into stg from the flat file. And from there into main.Before the main table is loaded,I need to validate if the data in all the columns of the row is matching.The main challenge is I have 100 columns in these tables. Can any one recommend me of a optimal solution to compare data across the tables ?The bottom line is I should not create duplicate rows into the main table.

Thanks in Advance.
Post #729531
Posted Friday, June 05, 2009 4:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550

Suggest you look at EXCEPT (and maybe INTERSECT) in BOL


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #729548
Posted Friday, June 05, 2009 8:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:10 AM
Points: 1,800, Visits: 1,512
search google on TableDiff utility, it comes with SQL 2005 installation used for table comparisons just for the kind of task u r looking for.
Post #729791
Posted Tuesday, June 16, 2009 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:58 AM
Points: 28, Visits: 45
Sincere Thanks for coming up with your suggestions.At this point I have tried out Except operator and this seems to be working reasonably well for me.
Post #735559
Posted Wednesday, June 17, 2009 8:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 PM
Points: 734, Visits: 3,561
rvkhadava (6/5/2009)
I have two tables, stg,main with the same structure.Each day data is refeshed into stg from the flat file. And from there into main.Before the main table is loaded,I need to validate if the data in all the columns of the row is matching.The main challenge is I have 100 columns in these tables. Can any one recommend me of a optimal solution to compare data across the tables ?The bottom line is I should not create duplicate rows into the main table.

Thanks in Advance.


Hi,

I have implemented the same task using SSIS and it works like a charm. I have used lookup table task in SSIS.

http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx ===> use method 2, it works for me.

Hope this helps,

\\K
[http://sqlquest.blogspot.com/]


______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #736632
Posted Thursday, July 08, 2010 6:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:41 AM
Points: 333, Visits: 156
Hi,

Looking for a method of comparing rows in the same table, I ran across this thread. I thought I would add my 2p to the debate:

SET @result = 0

IF EXISTS (SELECT EX.DocumentID FROM
(SELECT DocumentID, DocTitle, SortLetter, SortNumber FROM
PMProcessProcedures WHERE PMProcessID = @ID
EXCEPT
SELECT DocumentID, DocTitle, SortLetter, SortNumber FROM
PMProcessProcedures WHERE PMProcessID = @Approved) EX)

SET @result = 1

This will set @result if the two rows are different.

Hope this helps someone...
Post #949239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse