Re: Running a Update query based on information from Insert Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Wed, 23 Jul 2008 10:39:08 -0700, Wayne wrote:

(snip)
What I am trying to accomplish with the code below is that the system will
go in and insert any new records that it finds and then if it finds records
that need updated it would call the update stored procedure to update the
current records in the database based on the information that is in the temp
tables.

Hi Wayne,

This is a common problem, and in SQL Server 2008 you can solve this with
the new MERGE statement, that allows you to combine inserting new rows
and updating existing rows in a single statement.

However, in SQL Server 2005 (and before), you still need to execute both
an UPDATE and an INSERT command. Preferably in that order, since you
would otherwise update your just-inserted rows (setting columns to the
same data, so you would not get incorrect results, but you would lose
performance).

The basic pattern is:

UPDATE Target
SET Column1 = Source.Column1,
Column2 = Source.Column2,
...,
ColumnN = Source.ColumnN
FROM Target
INNER JOIN Source
ON Source.PrimaryKey1 = Target.PrimaryKey1
AND Source.PrimaryKey2 = Target.PrimaryKey2;

INSERT INTO Target
(PrimaryKey1, PrimaryKey2,
Column1, Column2, ..., ColumnN)
SELECT PrimaryKey1, PrimaryKey2,
Column1, Column2, ..., ColumnN
FROM Source
WHERE NOT EXISTS
(SELECT *
FROM Target
WHERE Target.PrimaryKey1 = Source.PrimaryKey1
AND Target.PrimaryKey2 = Source.PrimaryKey2);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.