Help with an Update/Insert procedure

Hello,
I'm new to SQL in general and I'm looking for the most efficient stored
procedure that can accomplish the following:
If aRecord with field "RecID" = # is found in aTable then
{
If aRecord's field "Updateable"=True then
UPDATE aRecord...
}
Else
{
INSERT aNewRecord into aTable ...
}
Basically, I'm submitting the field parameters for a record to a table in
the stored procedure. If a record already exists with the same primary key
in that table , then, if a field's value in that existing record isn't
"False", update it. Otherwise, if there is no record with that primary key
value, then insert this new record into the table.
Thanks and let me know if this doesn't make any sense!
WykAssuming "RecID" (what a horrible column name!) is a primary key, probably
most efficient is to try to update it first, and if it didn't work, insert.
UPDATE ... WHERE RecID = @.RecID
IF @.@.ROWCOUNT = 0
INSERT ...
You could do something slightly more elaborate, to make the code more
readable, with negligible performance differences:
IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
UPDATE ...
ELSE
INSERT ...
You're going to have to test in your own environment to be sure, of course.
Most efficient on my schema is not necessarily most efficient on your
schema.
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Here's what I've tried which doesn't seem to work though it compiles...
PROCEDURE [dbo].[p_MergeRecordIntoTable]
(
@.Id bigint,
@.Field1 nvarchar(50)
@.Updateable bit
)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*)
FROM [aTable]
WHERE ( Id= @.Id)
IF @.@.ROWCOUNT != 0
BEGIN
UPDATE [MergeTable]
SET
Field1= @.Field1
WHERE
( Id= @.Id )
AND
( Updateable = 1 )
END
ELSE
BEGIN
INSERT INTO [SI_MLB_Games] (
ID,
Field1,
Updateable,
VALUES (
@.Id,
@.Field1,
@.Updateable)
END
END
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Aaron, I was able to make it work perfectly with the your second example.
Not sure how I could make it work with the first though.
Big thanks!
Wyk
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OErAwLAPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Assuming "RecID" (what a horrible column name!) is a primary key, probably
> most efficient is to try to update it first, and if it didn't work,
> insert.
> UPDATE ... WHERE RecID = @.RecID
> IF @.@.ROWCOUNT = 0
> INSERT ...
> You could do something slightly more elaborate, to make the code more
> readable, with negligible performance differences:
> IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
> UPDATE ...
> ELSE
> INSERT ...
> You're going to have to test in your own environment to be sure, of
> course. Most efficient on my schema is not necessarily most efficient on
> your schema.
>
>
> "Wyk" <wykananda@.hotmail.com> wrote in message
> news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
>