Upsert Statement In SQL Server 2005

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)

Upsert Statement In SQL Server 2005

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-31 : 03:03:37

How to write an Upsert Statement while working with SQL Server 2005... examples will be appriciated...

Thanks
MIZ

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-31 : 03:07:06

Simple update statement (1 table)


update u
set    cola = 100
from   table1 u

Update statement involving more than 1 table


update u
set    cola = colb
from   table1 u inner join table2 b
on     u.somecol   = b.somecol

Look up Books Online for more information on the syntax etc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-31 : 03:14:02

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-31 : 03:42:47

He is asking about UPSERT not UPDATE!!

UPSERT - Which will update a record if it is already exists in the table else it will insert the record.

Some database like Oracle having this feature.

Anyone working on 2005 can update him.

--------------------------------------------------
S.Ahamed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-31 : 03:53:56

oh.

there isn't one in SQL Server.

You have to do it in 2 statement. INSERT and UPDATE.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-31 : 03:57:17

also see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67319
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58353
http://www.sqlservercentral.com/columnists/sjones/wheredoiwanttogotodayupsert.asp

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-31 : 04:08:58

Some one told me that in SQL Server 2000 this feature was not avaliable but in SQL Server 2005 it is avaliable ... can any one help on this?

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-31 : 04:23:02

quote:
Originally posted by Starlet_GT

Some one told me that in SQL Server 2000 this feature was not avaliable but in SQL Server 2005 it is avaliable ... can any one help on this?

refer below link, hopes ll help u,

http://209.34.241.68/mat_stephen/archive/2005/08/31/410022.aspx

UPDATE Destination FROM (Source INTERSECT Destination)

INSERT INTO Destination FROM (Source EXCEPT Destination)

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-31 : 04:25:15

some more stuff

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.MatchingField , MyTable.MatchingField
WHEN MATCHED THEN
UPDATE UpdateField = MyTempTable.UpdateField
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField,MyTempTable.UpdateField)

thanks,

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-31 : 09:28:17

quote:
Originally posted by mahesh_bote

some more stuff

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.MatchingField , MyTable.MatchingField
WHEN MATCHED THEN
UPDATE UpdateField = MyTempTable.UpdateField
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField,MyTempTable.UpdateField)

thanks,

Mahesh

Will this work in SQL Server 2005?

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-31 : 15:36:09

I've not really messed with the INTERSECT/EXCEPT operators.. but it looks like they are a different way of achomplishing the same thing we have always had to do to perform an "UPSERT."

Assuming you are dealing a single item, the basic gist is:[CODE]UPDATE MyTable
SET <field> = <value>
...
WHERE <field> = <key>

IF @@ROWCOUNT = 0
BEGIN
INSERT MyTable (<fields) VALUES (<values>)
END
[/CODE]

Hopefully, that makes sense. There are other options of course, but that is the basic way to do it in MS SQL.

pootle_flump

1064 Posts

Posted - 2007-05-31 : 18:45:43

quote:
Originally posted by madhivanan

quote:
Originally posted by mahesh_bote

some more stuff

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.MatchingField , MyTable.MatchingField
WHEN MATCHED THEN
UPDATE UpdateField = MyTempTable.UpdateField
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField,MyTempTable.UpdateField)

thanks,

Mahesh

Will this work in SQL Server 2005?

Madhivanan

Failing to plan is Planning to fail

Apologies if this was covered in the links - I did not check them. MERGE was in the 2005 beta but did not make it to the release. It is planned for 2008 I hear.

I have tried to pay with the difference\ intersect operators (or whatever they are called in 2005) for upserts. Every example I have seen is like Mahesh's pseudo code. It looks like it would work great. My experience in trying to code it, though, is it works fine for pk\ unique key upserts but gets really convoluted for attribute updates to the point that it was easier to use Lamprey's code. Since most updates are on attributes it makes this idea next to useless. if someone has fully functioning code to enlighten me I would be very interested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources