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

compare data row by row Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 1:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
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
Posted Wednesday, March 13, 2013 2:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430623
Posted Wednesday, March 13, 2013 2:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:21 AM
Points: 176, Visits: 346
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
Posted Wednesday, March 13, 2013 2:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430628
Posted Wednesday, March 13, 2013 3:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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.



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 »

Add to briefcase

Permissions Expand / Collapse