Running a Update query based on information from Insert Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wayne - 23 Jul 2008 18:39 GMT

I am trying to setup a script where I run an insert query to insert
information into a table.
The first part I have down pat as far as the Insert, where I'm having
difficulties is that the information is coming from an outside source and the
input file that goes into the temp table has both new records that need to be
inserted but also might have records that are already in the table that need
to be updated.
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.
I've tried a CASE statement and it didn't work and have tryed a Try/Catch
that didn't work. I had thought just to run the Insert query and then go back
and run the update query but then I end up with 2 records.

INSERT INTO Transportation
(personid, calendarid, inbus, intime, inbusstop, outbus,
outbusstop,milestransported)
SELECT personid, calendarid,inbus, intime, inbusstop, outbus,
outbusstop,milestransported
FROM Transportation1 t1
WHERE NOT EXISTS
(SELECT * FROM Transportation as t
WHERE t.personid = t1.personid AND t.calendarid = t1.calendarid AND t.inbus
= t1.inbus)
GO
EXEC sp_TransportationUpdate
GO

Thanks in Advance for any suggesstions

Signature

Wayne Hess

Reply to this Message

Hugo Kornelis - 24 Jul 2008 21:24 GMT

(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);

Signature

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

Reply to this Message