Replace into..??

[Michael Posthoff]

Hallo Group,

is there an equivelant/replacement of the MySQL Command "Replace Into" for
Access/MS SQLServer?

"Replace Into" does either inserting or updating of data, depending on
whether the record exists or not. And I would love to avoid 2 differnet
functions for this.

regards,
Michael Posthoff

[Bob Barrows [MVP]]

Michael Posthoff wrote:

> Hallo Group,
>
> is there an equivelant/replacement of the MySQL Command "Replace
> Into" for Access/MS SQLServer?
>
> "Replace Into" does either inserting or updating of data, depending on
> whether the record exists or not. And I would love to avoid 2
> differnet functions for this.

No, you have to do a two-stage process: run an update query to update
records that meet the requirements. If no records meet the requirements,
insert the new records.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [MVP]]

That is on the WishList for SQL Server (most people coin it as an "upsert").
However, it is not implemented in current versions of SQL Server, and is
unlikely to make the next version, either.

The usual workaround is a single stored procedure (you don't use a function
to insert/update!) that takes the input parameters, then has logic like
this:

IF EXISTS (SELECT 1 FROM tbl WHERE [primary key] = @param2)
UPDATE tbl SET whatever = @param1 WHERE [primary key] = @param2
ELSE

INSERT tbl(whatever, [primary key]) VALUES(@param1, @param2)

Or this:

UPDATE tbl SET whatever = @param1 WHERE [primary key] = @param2
IF @@ROWCOUNT = 0
INSERT tbl(whatever, [primary key]) VALUES(@param1, @param2)

For Access, you would have to use a stored query (assuming those can handle
multiple DML statements!) or perform two round-trips, but the logic would
essentially be the same.

Or, the previous page can have a flag that is passed, depending on whether
someone chose "add" or "edit"...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]





"Michael Posthoff" <posthoff@e-confirm-quatsch.de> wrote in message
news:c5ito9$mkb$1@news1.transmedia.de...

> Hallo Group,
>
> is there an equivelant/replacement of the MySQL Command "Replace Into" for
> Access/MS SQLServer?
>
> "Replace Into" does either inserting or updating of data, depending on
> whether the record exists or not. And I would love to avoid 2 differnet
> functions for this.
>
> regards,
> Michael Posthoff
>
>
>