compare data row by row

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » compare data row by row

compare data row by row

Rate Topic

Display Mode

Topic Options

Author

Message

SQL_NuB

Posted Wednesday, March 13, 2013 1:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 12:18 PM
Points: 175, Visits: 340

I have a request and I'm unable to use 3rd party tools such as RedGate. I'll be creating a database that will have staging tables and history tables. Prior to the data going to the history tables I need to compare the data between both tables and send out notifications if any of the data has been changed, deleted, or added.

What is the best way to do this without the use of 3rd party tools? As of now I'm not sure of a record count for each table, but its 10 tables and the compare process will be from 2 - 5 columns per table.

Post #1430596

Sean Lange

Posted Wednesday, March 13, 2013 2:06 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 8,967, Visits: 8,530

You are going to have to write your own queries for this. This type of thing is generally done using left joins from one table to the other. Without any more specifics that is about all I can offer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1430623

SQL_NuB

Posted Wednesday, March 13, 2013 2:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 12:18 PM
Points: 175, Visits: 340

i'm creating the query/process to do the data compare, I was wondering if this could be done using SSIS or do queries have to be written.

Post #1430624

Sean Lange

Posted Wednesday, March 13, 2013 2:11 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 8,967, Visits: 8,530

You could use SSIS but somewhere along the way you will have to write some queries for this level of custom data comparison.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1430628

Lynn Pettis

Posted Wednesday, March 13, 2013 3:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

Create us a sandbox. A couple (perhaps 2 to 4) of tables, some sample data for the tables (not a lot, just enough to cover the problem domain), and what you want to see happening depending on what happens with queries.

You need to provide this a DDL for the tables, series of INSERT INTO statements for the sample data, possible the same for the expected results.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1430650

« Prev Topic | Next Topic »

Permissions