• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

SQL - compare difference in tables

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
tommym121
Asked:
tommym121
  • 5
  • 5
  • 4
  • +2
1 Solution
 
TempDBACommented:
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
 
waltersnowslinarnoldCommented:
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
 
jogosCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jogosCommented:
<<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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
-- 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
 
jogosCommented:
Trigger with modification date is no sollution for deleted rows.
0
 
Scott PletcherSenior DBACommented:
>>  I would only update  the row that has changes. <<
0
 
tommym121Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
tommym121Author Commented:
Sorry Scott.  Any good suggestion?  Or where I should look into?  Thanks.
0
 
Scott PletcherSenior DBACommented:
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
 
tommym121Author Commented:
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
 
jogosCommented:
<<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
 
tommym121Author Commented:
Thanks
0
 
jogosCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now