sql server new users: UPDATE OR INSERT in one statement? -- sql server new users

UPDATE OR INSERT in one statement?

Ralf Kaiser
10/31/2007 12:00:00 AM

sql server new users:

Hello,

is it possible to create one statement that updates a table if a record is
already present and inserts new data if the record does not exist before?

Something like (Pseudo-Code!!!):

UPDATE_OR_INSERT MyTable
SET NAME = 'Bill', USERID=5
WHERE USERID=5

That should update the field NAME if USERID could be found and otherwise
insert a new record.

I know that some other SQL servers offer such things (Firebird IIRC) but
does MS SQL Server offer this too? Currently i do that with a select command
and depending on the result i execute an UPDATE or an INSERT statement.

TIA,
Ralf

Re: UPDATE OR INSERT in one statement?

Andrew J. Kelly
10/31/2007 9:03:40 AM

Not with any of the released products. SQL2008 will include a MERGE command
that does exactly this. Currently you need to do something like this:

UPDATE MyTable SET Name = 'Bill
WHERE UserID = 5
IF @@ROWCOUNT = 0
INSERT INTO MyTable (Name, USERID) VALUES ('Bill,5)

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

[quoted text, click to view]

Re: UPDATE OR INSERT in one statement?

Steve Dassin
10/31/2007 4:24:12 PM

MERGE is like PIVOT, just some syntatic sugar:) The real issue is having
a 'view' aware of relationships between tables and making a decision
of what operation to perform when it is hit with a row. This is what
I would like in Sql Server:
http://beyondsql.blogspot.com/2007/06/dataphor-intelligent-views.html

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.