"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]

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]

Bart op de grote markt (ba********@freegates.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Bart op de grote markt]

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]

On Feb 6, 7:28 am, "Bart op de grote markt" <[e-mail]>
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/2...nt-in-sql.html