"insert on existing update" in MS SQL Server?

[Bart op de grote markt]

Hello

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.

Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

Greetz,

Bart

[Serge Rielau]

Re: "insert on existing update" in MS SQL Server?

Bart op de grote markt wrote:

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

In ANSI/SQL it's called a MERGE statement.
In SQL Server I'd do an UPDATE FROM, if no row found follow up with an
INSERT.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Erland Sommarskog]

Re: "insert on existing update" in MS SQL Server?

Bart op de grote markt (bartwarnez@fre egates.be) writes:

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

I'm afraid that you will have to chalk up one minus for MS SQL Server.

You will have to do:

UPDATE ...

INSERT ...
SELECT ...
WHERE NOT EXISTS (....)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Bart op de grote markt]

Re: "insert on existing update" in MS SQL Server?

Ok, thank you all for the replies! It's a pity but well :-). If MS
reads this post: hello MS, you know what to do next.


Grtz,

Bart

[Alex Kuznetsov]

Re: "insert on existing update" in MS SQL Server?

On Feb 6, 7:28 am, "Bart op de grote markt" <bartwar...@fre egates.be>
wrote:

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).
>
Greetz,
>
Bart

Also look up "Mimicking MERGE Statement in SQL Server 2005 ":

http://sqlserver-tips.blogspot.com/2006/09/mimicking-merge-statement-in-sql.html