How can I write this MySQL query as T-SQL?

Question

REPLACE INTO blastersmembers (member_id, name, member_group_id, email, joined,
/*  etc.  */

I haven't included the whole thing because I don't believe it to be relevant. I basically want to insert a row if it doesn't exist, otherwise replace the existing row.

Sounds like you are looking for the MERGE INTO SQL statement. http://technet.microsoft.com/en-us/library/bb510625.aspx See this SO article for more info: http://stackoverflow.com/questions/11428386/is-a-replace-into-query-good-practice — Pete Garafano, Jul 29 '13 at 23:46

score 5 · Answer 1 · edited May 23 '17 at 12:11

REPLACE INTO or an equivalent doesn't exist on MS SQL explicitly, but you can do an UPDATE first, and if that did not update anything, do an insert. That would look something like this:

update blastersmembers set name = @name, member_group_id = @member_group_id, email = @email, joined = @joined, ... 
where member_id = @member_id
if @@rowcount = 0
insert into blastersmembers (member_id, name, member_group_id, email, joined, ...) values (...)

Credit to: Insert Update stored proc on SQL Server

Eli Algranti · Answer 2 · 2013-07-30T04:29:30.173

If you're using SQL server 2008 and later the MERGE command does that and more:


-- @member_id, @member_group_id are the ids of the record you want to match

merge blastermembers as target
    using (select @member_id, @member_group_id) as source (member_id, member_group_id)
    on source.member_id = target.member_id 
           and source.member_group_id = target.member_group_id
    when matched then
        update set name = @name -- etc
    when not matched then
        insert (member_id, name, member_group_id) 
                   values (source.member_id, name, source.member_group_id);

Edit: @swasheck mentions problems with merge when using some indexed views in unpatched servers, or on old servers. This should not prevent you from using MERGE which is the right command to use and has the advantage of not requiring a transaction.

How can I write this MySQL query as T-SQL?

2 Answers2