Stored Procedure to compare tables

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 2008 Forums

Transact-SQL (2008)

Stored Procedure to compare tables

Author

Topic

maltman
Starting Member

8 Posts

Posted - 2011-03-09 : 10:09:55

Hello all,

I have a stored procedure that I have created that will compare two tables and either insert new records from one to the other, update the records with current values, or delete records that no longer exist.

However, I am having an issue with the insert statement inserting duplicate records and the update statement is creating a new ID instead of comparing the IDs and updating that record.

Here is the stored procedure:


CREATE PROC [dbo].[sp_CheckFileInfo]

AS

BEGIN

SET NOCOUNT ON

INSERT INTO FileInfo(FileInfoID, FileName, DateCreated, DateModified, FileSize, FullPath, FlagID)
	Select FileInfoID, FileName, DateCreated, DateModified, FileSize, FullPath, FlagID
	From Temp_FileInfo
	Where NOT EXISTS (select 1 from FileInfo F where F.FileInfoID = Temp_FileInfo.FileInfoID)

Merge FileInfo AS f
Using Temp_FileInfo As t
ON f.FullPath = t.FullPath
When Matched AND f.FileName <> t.FileName OR
f.DateModified <> t.DateModified OR
f.FileSize <>t.FileSize OR
f.FullPath <> t.FullPath
Then Update
Set f.FileInfoID = t.FileInfoID,
f.FileName = t.FileName,
f.DateCreated = t.DateCreated,
f.DateModified = t.DateModified,
f.FileSize = t.FileSize,
f.FullPath = t.FullPath,
f.FlagID = 2;

END

Any help would be greatly appreciated.

Thanks,

Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 11:08:17

your condition is wrong. one place you're comparing on FullPath whilst in insert its on FileInfoID. whats the actual field on which you've to do the comparison?
also since insert happens first those records will also be picked again by MERGE and as per current update the FileInfoID will get updated again.
If you're using MERGE you can handle everything inside that so no need of separate insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maltman
Starting Member

8 Posts

Posted - 2011-03-09 : 11:35:00

Thank you for the reply.

I wanted to use FileInfoID for all comparisons but I was running into issues with it changing so I started using FullPath.

I tried to get it set up all in the merge statement but I always get an incorrect syntax error when I throw in WHEN NOT MATCHED.

Here is the Merge statement I have been trying to get to work:

Merge FileInfo AS f
Using Temp_FileInfo As t
ON f.FullPath = t.FullPath
When Matched AND f.FileName <> t.FileName OR
f.DateModified <> t.DateModified OR
f.FileSize <>t.FileSize OR
f.FullPath <> t.FullPath
Then Update
Set f.FileInfoID = t.FileInfoID,
f.FileName = t.FileName,
f.DateCreated = t.DateCreated,
f.DateModified = t.DateModified,
f.FileSize = t.FileSize,
f.FullPath = t.FullPath,
f.FlagID = 2
WHEN NOT MATCHED
INSERT (FileInfoID, FileName, DateCreated, DateModified, FileSize, FullPath, FlagID)
VALUES (t.FileInfoID, t.FileName, t.DateCreated, t.DateModified, t.FileSize, t.FullPath, t.FlagID)

I have several errors though.

A merge statement must be terminated by a ;
Incorrect syntax on the last Insert
And invalid column names

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 12:33:14

you're missing a last ;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maltman
Starting Member

8 Posts

Posted - 2011-03-09 : 12:49:45

I added the ; and that error went away but I still have three errors.

Incorrect Syntax near WHEN (The second when)

Invalid column names for all of my insert columns.

The multi-part identifier could not be bound for all of my values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 12:53:37

you're missing a THEN also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maltman
Starting Member

8 Posts

Posted - 2011-03-09 : 13:04:06

I think that did it.

I still have a problem with the FileInfoID but I think that is a problem with my initial load of the Temp table.

Thank you for the assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources