Solved

SQL - compare difference in tables

Posted on 2012-03-26
16
317 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
ID: 37772202
>>  I would only update  the row that has changes. <<
0
 

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:Scott Pletcher
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:Scott Pletcher
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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