March 13, 2013 at 1:23 pm
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.
March 13, 2013 at 2:06 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 2:09 pm
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.
March 13, 2013 at 2:11 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 3:05 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply