Compare difference and output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare difference and output

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-07-08 : 11:09:27

Hi all,Hopefully this is a quicky.

I want to run a query today into a table.
Then Tomorrow I want to run the same query.
I want to output any new records that have been added since yesterday.
Whats the best way to go about this.
Some sort of comparison query?

eg: today I run select refnumber from tbldata into tbla
tomorrow I run select refnumber from tbledata where refnumber doesn't already exist in tbla ??
Please help.
Ray..

cyberjunkyks
Starting Member

2 Posts

Posted - 2008-07-08 : 11:21:03

Look at Except. It makes this kind of thing simple.

http://msdn.microsoft.com/en-us/library/ms188055.aspx

Ken Simmons

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:43:23

It depends on how your incremental changes occur. If you have only inserts happening on main table you can use EXCEPT. however if your table has all actions like update,insert,delete happening then you want a procedure of this kind.

CREATE PROC CaptureIncData
@Date datetime
AS
INSERT INTO DestTable
SELECT fields
FROM SourceTable s
LEFT JOIN DestnTable d
ON d.KeyCol=s.KeyCol
WHERE d.KeyCol IS NULL
AND s.CreatedDate>@Date

UPDATE d
SET d.fields=s.fields
FROM DestTable d
INNER JOIN SourceTable s
ON d.KeyCol=s.KeyCol
WHERE s.createddate > @Date


DELETE d
FROM DestTable d
LEFT JOIN SourceTable s
ON d.KeyCol=s.KeyCol
WHERE s.KeyCol IS NULL
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources