Stored Procedure to UPDATE or INSERT

24 Aug 2006 2:37 PM

wnfisba

We need to write a Stored Procedure that will UPDATE or INSERT and store
values that exist within a Staging table. The dilemma is that the data may or
may not be in our target table. How can we discern to either UPDATE the row
in the target table if its there or INSERT the row if it does not yet exist
within the target table???

Is there any error handling that can be done via a SQL statement that would
enable us to do this dymaically???

Any help would be GREATLY appreciated.

Thanks!

wnfisba

24 Aug 2006 2:43 PM

Mike C#

Something like this?

CREATE PROCEDURE dbo.Upsert @id INT, @value VARCHAR(2000)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM MyTable WHERE [id] = @id) > 0
        UPDATE MyTable
        SET value = @value
        WHERE id = @id
    ELSE
        INSERT INTO MyTable ([id], value)
        VALUES (@id, @value)
END

Too bad they did away with the MERGE statement before RTM :(

Show quote

"wnfisba" <wnfi***@discussions.microsoft.com> wrote in message
news:FCECD55D-E3F2-458E-A1EE-BDAB7116C01A@microsoft.com...
> We need to write a Stored Procedure that will UPDATE or INSERT and store
> values that exist within a Staging table. The dilemma is that the data may
> or
> may not be in our target table. How can we discern to either UPDATE the
> row
> in the target table if its there or INSERT the row if it does not yet
> exist
> within the target table???
>
> Is there any error handling that can be done via a SQL statement that
> would
> enable us to do this dymaically???
>
> Any help would be GREATLY appreciated.
>
> Thanks!
>
> wnfisba

24 Aug 2006 7:02 PM

wnfisba

Mike,

Do I need the "> 0" since the EXISTS is present???

That structure looks a little bit funky....

Show quote

"Mike C#" wrote:

> Something like this?
>
> CREATE PROCEDURE dbo.Upsert @id INT, @value VARCHAR(2000)
> AS
> BEGIN
>     IF EXISTS (SELECT 1 FROM MyTable WHERE [id] = @id) > 0
>         UPDATE MyTable
>         SET value = @value
>         WHERE id = @id
>     ELSE
>         INSERT INTO MyTable ([id], value)
>         VALUES (@id, @value)
> END
>
> Too bad they did away with the MERGE statement before RTM :(
>
> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message
> news:FCECD55D-E3F2-458E-A1EE-BDAB7116C01A@microsoft.com...
> > We need to write a Stored Procedure that will UPDATE or INSERT and store
> > values that exist within a Staging table. The dilemma is that the data may
> > or
> > may not be in our target table. How can we discern to either UPDATE the
> > row
> > in the target table if its there or INSERT the row if it does not yet
> > exist
> > within the target table???
> >
> > Is there any error handling that can be done via a SQL statement that
> > would
> > enable us to do this dymaically???
> >
> > Any help would be GREATLY appreciated.
> >
> > Thanks!
> >
> > wnfisba
>
>
>

24 Aug 2006 8:31 PM

Mike C#

No, that's a typo. I originally did a SELECT COUNT(*) ... > 0 and then
converted it to EXISTS. Leave the > 0 off.

Show quote

"wnfisba" <wnfi***@discussions.microsoft.com> wrote in message
news:472ADAC1-AB47-423A-AA4B-3F0C083BB7B3@microsoft.com...
> Mike,
>
> Do I need the "> 0" since the EXISTS is present???
>
> That structure looks a little bit funky....
>
>
> "Mike C#" wrote:
>
>> Something like this?
>>
>> CREATE PROCEDURE dbo.Upsert @id INT, @value VARCHAR(2000)
>> AS
>> BEGIN
>>     IF EXISTS (SELECT 1 FROM MyTable WHERE [id] = @id) > 0
>>         UPDATE MyTable
>>         SET value = @value
>>         WHERE id = @id
>>     ELSE
>>         INSERT INTO MyTable ([id], value)
>>         VALUES (@id, @value)
>> END
>>
>> Too bad they did away with the MERGE statement before RTM :(
>>
>> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message
>> news:FCECD55D-E3F2-458E-A1EE-BDAB7116C01A@microsoft.com...
>> > We need to write a Stored Procedure that will UPDATE or INSERT and
>> > store
>> > values that exist within a Staging table. The dilemma is that the data
>> > may
>> > or
>> > may not be in our target table. How can we discern to either UPDATE the
>> > row
>> > in the target table if its there or INSERT the row if it does not yet
>> > exist
>> > within the target table???
>> >
>> > Is there any error handling that can be done via a SQL statement that
>> > would
>> > enable us to do this dymaically???
>> >
>> > Any help would be GREATLY appreciated.
>> >
>> > Thanks!
>> >
>> > wnfisba
>>
>>
>>

24 Aug 2006 3:06 PM

Roy Harvey

If I understand your problem, the usual approach is to us an UPDATE
first, which will only update matching rows, then an INSERT to insert
the missing rows.

UPDATE Target
SET .....
FROM Staging
WHERE Target.pk = Staging.pk

INSERT Target
SELECT .....
FROM Staging
WHERE NOT EXISTS
(select * from Target where Target.pk = Staging.pk)

Roy Harvey
Beacon Falls, CT

On Thu, 24 Aug 2006 07:37:01 -0700, wnfisba
<wnfi***@discussions.microsoft.com> wrote:

Show quote

>We need to write a Stored Procedure that will UPDATE or INSERT and store
>values that exist within a Staging table. The dilemma is that the data may or
>may not be in our target table. How can we discern to either UPDATE the row
>in the target table if its there or INSERT the row if it does not yet exist
>within the target table???
>
>Is there any error handling that can be done via a SQL statement that would
>enable us to do this dymaically???
>
>Any help would be GREATLY appreciated.
>
>Thanks!
>
>wnfisba