Why not an INSUPDATE type command? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Why not an INSUPDATE type command?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-19 : 00:40:00

I've been noticing that I am frequently typing more or less the same code:


DECLARE @iTmp int

select @iTmp=i from table where i_users=@iUsers
if @iTmp is null
  BEGIN
    insert into table (i_users,value) VALUES (@iUsers,@iValue)
    select @iTmp=SCOPE_IDENTITY() 
  END
Else
  update table set i_users=@iUsers,value=@iValue where i=@iTmp

Now, I know there are other ways to do that: do the update, and check rowcount, etc. But it seems to be it would be very handy if I could tell SQL server "either update the existing row, or create a new one if it's not already there." It would reduce errors, make code more maintainable, and probably offer better performance.

It would probably require a unique index on the fields being used for the row lookup (so you know that only one row will be affected whether it's an INSERT or an UPDATE). But that should be fine.

So why not? I don't terribly mind doing the work myself, it just seems like wasted repetitive effort when SQL server could, take care of it.

Cheers
-b

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-19 : 00:58:34

That TSQL can simplied to:


IF EXISTS(Select 1 from Table where i_users = @IUsers)
update table set i_users=@iUsers,value=@iValue where i=???
ELSE
insert into table (i_users,value) VALUES (@iUsers,@iValue)

DavidM

Tomorrow is the same day as Today was the day before.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-19 : 01:44:56

What if you wanted it to do something different if you tried to insert duplicate values ?

What if it could insert as long as they were on different days ?

etc etc etc etc

I would rather have the tools to create my own logic that be second guessed by a software vendor and have limited flexability.

That is why I don't use Cold Fusion

Damian

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-19 : 03:17:07

Rumor has it that the next version of SQL Server (code named Yukon) will have an 'UPSERT' statement that should provide this functionality.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-19 : 04:32:08

I guess that is better than an INDATE feature

Damian

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-19 : 04:40:31

Actually, I'm going to e-mail sqlwish@microsoft.com and ask them for an UPTRUNCATE statement. Now THAT would be entertaining!

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-22 : 00:54:45

Yeah, well, what about a DELINS command? (delete a row if it exists, otherwise insert it).

-b

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-22 : 02:21:20

quote:

Yeah, well, what about a DELINS command? (delete a row if it exists, otherwise insert it).

Hmmmm... that would seem to have some interesting possibilities, too!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources