insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:

>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP