How to achieve MySQL REPLACE INTO functionality in SQL Server 2005?

Data and Databases
1

I’m working with SQL Server 2005 and need to replicate the behavior of MySQL’s REPLACE INTO statement. This MySQL command is really handy because it automatically inserts a new record or updates an existing one if there’s a duplicate key.

-- Example of what I want to achieve
-- Instead of separate INSERT/UPDATE logic
IF EXISTS (SELECT 1 FROM employees WHERE emp_id = @id)
    UPDATE employees SET name = @empName, salary = @empSalary WHERE emp_id = @id
ELSE
    INSERT INTO employees (emp_id, name, salary) VALUES (@id, @empName, @empSalary)

Right now I have to write separate logic to check if a record exists, then decide whether to insert or update. This means maintaining two different SQL statements in my application code, which gets messy.

Is there a built-in way or a standard pattern in SQL Server 2005 to handle this insert-or-update scenario more elegantly? I’m looking for something that works consistently across different tables without having to write custom logic each time.

2

you could also use a temp table - dump your data there first, then run one statement that covers both scenarios. really handy when you’re dealing with multiple records and skips all the rowcount checking.

3

SQL Server 2005 doesn’t have MySQL’s REPLACE INTO, but here’s a clean way to do it with UPDATE and INSERT:

UPDATE employees 
SET name = @empName, salary = @empSalary 
WHERE emp_id = @id

IF @@ROWCOUNT = 0
    INSERT INTO employees (emp_id, name, salary) 
    VALUES (@id, @empName, @empSalary)

Update first, then check @@ROWCOUNT to see if any rows changed. No rows updated? Insert it. This beats doing a separate EXISTS check and cuts down on database round trips. It’s atomic and way more efficient than checking if the record exists first.

4

Interesting challenge! A stored procedure might work well here. Quick questions though - are you doing single row operations or batch inserts? And what’s keeping you on 2005? Also, is the current if/else method causing performance issues?