Create Record - SQL Server Forums

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

Transact-SQL (2005)

Create Record

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-01 : 06:58:11

Hello,

I have a table with only two columns:
LevelId[GUID, uniqueidentifier] and LevelName [nvarchar(100)].

I need to create a record by providing LevelName as a parameter.

However, when there is already a record which such LevelName I want to update that record instead of creating a new one.

How can I do this?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 07:17:04

UPDATE Table1
SET Col1 = @NewValue
WHERE ColA = @SomeIdentity

IF @@ROWCOUNT = 0
INSERT Table1 (Col1, ColA) VALUES (@NewValue, @SomeIdentity)

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-01 : 07:27:43

Hi,

I did this in other way:

ALTER PROCEDURE [dbo].[by27_Levels_CreateLevel]

-- Define the procedure parameters
@LevelName NVARCHAR(100)

AS

-- Allows @@ROWCOUNT and the return of number of records when ExecuteNonQuery is used
SET NOCOUNT OFF;

-- Declare and define LevelId
DECLARE @LevelId UNIQUEIDENTIFIER;
SELECT @LevelId = LevelId FROM dbo.by27_Levels WHERE LevelName = @LevelName

-- Check if LevelId is Null
IF @LevelId IS NULL
BEGIN

-- Create new level in by27_Level
INSERT dbo.by27_Levels (LevelName)
VALUES (@LevelName)

END
ELSE
BEGIN

-- Update existing level in by27_Level
UPDATE dbo.by27_Level
SET LevelName = @LevelName
WHERE LevelId = @LevelId

END

Do you think this is a wrong way to do this?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 08:08:40

Now you do one SELECT more than necessary.

1) SELECT
2) INSERT or UPDATE.

Always two steps.

1) UPDATE
2) INSERT if no record was updated.

In most cases, only one operations is needed with the logic I suggested. If necessary, you can switch the UPDATE/INSERT order.

INSERT Table1 (Col1, ColA) VALUES (@NewValue, @SomeIdentity)

IF @@ROWCOUNT = 0
UPDATE Table1
SET Col1 = @NewValue
WHERE ColA = @SomeIdentity

It depends on the "most likely scenario". If the INSERTs are most likely to succeed, try that first and only update if there already exist a record.

Or, as in my first suggestion, if the most likely scenario is that the records already exist, try that first and if the case is that the records is not present, insert it.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 08:16:19

And for you logic to succeed, you must pass TWO parameters to the stored procedure.
How will else the INSERT work?

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-01 : 13:03:15

There is something I don't understand:

INSERT Table1 (Col1, ColA) VALUES (@NewValue, @SomeIdentity)
IF @@ROWCOUNT = 0
...

How can a INSERT command return a @@ROWCOUNT equal to 0?

Won't it always insert the record?

And when a record is inserted what is returned by the procedure?

Should I make something like "Return (0)" or "Return (1)" to indicate if the record was inserted or updated?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 13:38:10

IF record already exists (due to primary key) no record is inserted...

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-01 : 14:50:16

Peso,

Now I understood. However I don't want to provide the primary key which is a GUID. I only want to provide the LevelName.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 15:07:41

Then why do you need an UPDATE, if LevelID is primary key?

Peter Larsson
Helsingborg, Sweden