Solved

SQL - compare difference in tables

Posted on 2012-03-26
16
313 Views
Last Modified: 2012-04-06
I am trying to update an application with the data stored within a number of SQL table nightly.  I would only update  the row that has changes.

My plan is to use a select statement to query the data I need and store in a temporary table,
Next day I will create another table with the same select statement.  
I will then compare and extract the rows that are difference.

Is this a good way to do it?
Can someone guide me what I need to do to achieve this?  Thanks.
0
Comment
Question by:tommym121
  • 5
  • 5
  • 4
  • +2
16 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 37769745
You mean to say you will be doing it differentially right. If yes than it looks fine else you need to keep some tracking mechanism that identifies that what data you have used till yesterday so that the processing wouldn't take much time.
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37769778
You can achieve this by comparing the Source table with the Destination table by column's. use LEFT OUTER JOIN like below sample would help you;

SELECT
col1,
col2,
..
..
FROM DestinationTable d
LEFT OUTER JOIN SourceTable s
ON s.col1 = d.col1
AND s.col2 = d.col2
AND ..
AND ..
WHERE
s.col1 IS NULL
OR s.col2 IS NULL
OR ..
OR ..
0
 
LVL 25

Expert Comment

by:jogos
ID: 37771103
What will you do with the difference?

For comparing tables you can do this with a single MERGE-command that can handle new , deleted and updated records.
http://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
Merge tableA in dbA to tableA in dbB and
 update/insert/delete tableA in dbB at once
or save new version + action in change-table

Maybe good to look at Change Data Capture
http://msdn.microsoft.com/en-us/library/cc645937.aspx
http://www.codeproject.com/Articles/166250/Microsoft-SQL-Server-2008-Change-Data-Capture-CDC
0
 
LVL 25

Expert Comment

by:jogos
ID: 37771138
<<My plan is to use a select statement to query the data I need and store in a temporary table,>>
First duplication of your data  in CopyTable1
<<Next day I will create another table with the same select statement.  >>
Second duplication of your data CopyTable2

<<I will then compare and extract the rows that are difference.>>
Report it? Or store it again in a table? Or directly update it in your destionation as <<I am trying to update an application>> suggests?

And next day? Version of previous day is in CopyTable2, not in CopyTable1.

So no I don't think this is an efficient way to go.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37771516
To determine which rows were updated (and inserted?), add a mod_time column to the table, and use a trigger to maintain it.

You could then use an index on that column to find only the rows that had changed since you last checked it.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37771530
-- a default value will set the column for all INSERTs
ALTER TABLE table_name
ADD CONSTRAINT table_name__DF_mod_time DEFAULT GETDATE();


-- trigger used for updates
CREATE TRIGGER table_name_trg_upd
ON table_name
AFTER UPDATE
AS

UPDATE tn
SET mod_time = GETDATE()
FROM table_name tn
INNER JOIN inserted i ON
    i.key_col = tn.key_col --AND i.key_col2 = tn.key_col2 ...
GO


Yes, technically this doesn't verify that some column actually changed, but if the row UPDATEd, presumably it was for a reason.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37772053
Trigger with modification date is no sollution for deleted rows.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37772202
>>  I would only update  the row that has changes. <<
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:tommym121
ID: 37772881
I should mention,  I am trying to take the changes from a SQL database to CRM 2011.  
I have been using Select statement to combine multiple tables to create the data I need for different entities within CRM 2011
.
That is why I am trying to create temp table to host my data I need from select.

Is there a better way to do what I suggest?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37772916
Yeah, you really should of mentioned that in the original q -- it's a major factor obviously in what will work and/or be best.
0
 

Author Comment

by:tommym121
ID: 37781744
Sorry Scott.  Any good suggestion?  Or where I should look into?  Thanks.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37781918
I think you have some possibilities here.

You can use a trigger to mark the rows INSERT/UPDATEd using a datetime, and a trigger to capture a copy of DELETEd rows.

Or, if you really are in SQL 2008, you can use CDC to determine what has changed on the source table(s).

I would not consider trying to directly compare the SQL and the CRM to determine data differences.  Over time I think that would get extremely tricky.
0
 

Author Comment

by:tommym121
ID: 37792179
What I plan to do is
1.  I will query and create a table (table1) with the data I need to import to the CRM
2. Next day,  I will use the same query and create a second table(table2). I will like to:
    2.a compare the two table
    2.b extract the difference into a temporary table (not sure how to compare and put data into a temporary table)
    2.c  then import the temporary table into CRM.
    2.d drop the difference
    2.e  remove the table1 and rename table2
3. Repeat Step2 repeatedly.

I wonder if there is a easy to compare two tables and extract the rows I need.
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37792471
<<I wonder if there is a easy to compare two tables and extract the rows I need.>>
As I posted earlier, the MERGE-statement.  Only you must take care on the 'when matched' and you compare content not to fall in to the NULL-trap

So for you
- create a copy-table CrmContent (your table1)
- merge the CrmContent (as target) with the table /query (as source) you would use to fill your copy-tables (all of your point 2)
- use the output-clause to fill your difference-table  CrmContentChanges (your #table, step 2b)

Here an example on how it is used with different actions
http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/  (extract specific statement i refer to)
--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update 
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN 
DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
SELECT @@ROWCOUNT;
GO

Open in new window


Here syntax and also example on how to update the destination table in same statement insert the difference in a #table
http://msdn.microsoft.com/en-us/library/bb510625.aspx (extract specific statement i refer to)
    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
	WHEN NOT MATCHED THEN	
	    INSERT (UnitMeasureCode, Name)
	    VALUES (source.UnitMeasureCode, source.Name)
	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

Open in new window

0
 

Author Closing Comment

by:tommym121
ID: 37815015
Thanks
0
 
LVL 25

Expert Comment

by:jogos
ID: 37815232
Here another/better example of a insert starting from the output of a merge.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27659512.html#a37801395
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now