MS SQL Server - insert or replace

Author

insert or replace

nyhetsgrupper@gmail.com

2006-10-24, 6:33 pm

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?

Hari Prasad

2006-10-24, 6:33 pm

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?
>

Hugo Kornelis

2006-10-24, 6:33 pm

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

Sponsored Links