syntax for single row MERGE / upsert in SQL Server

by

i finally got the Upsert syntax using MERGE in SQL Server 2008. Using what Jacob wanted to do (an Upsert):

IF EXISTS(SELECT * FROM member_topic WHERE mt_member = 0 AND mt_topic = 110)
BEGIN
    --update existing row
    UPDATE member_topic SET mt_notes="test"
    WHERE mt_member = 0
    AND mt_topic = 110
END
ELSE
BEGIN
    --insert new row
    INSERT INTO member_topic (mt_member, mt_topic, mt_notes)
    VALUES (0, 110, 'test')
END

The equivalent MERGE syntax is:

MERGE member_topic
USING ( 
    VALUES (0, 110, 'test')
) AS foo (mt_member, mt_topic, mt_notes) 
ON member_topic.mt_member = foo.mt_member 
   AND member_topic.mt_topic = foo.mt_topic
WHEN MATCHED THEN
   UPDATE SET mt_notes = foo.mt_notes
WHEN NOT MATCHED THEN
   INSERT (mt_member, mt_topic, mt_notes)
   VALUES (foo.mt_member, foo.mt_topic, foo.mt_notes)
; --A MERGE statement must be terminated by a semi-colon (;).

More Related Contents:

  1. Using merge..output to get mapping between source.id and target.id
  2. Using a if condition in an insert SQL Server
  3. Atomic UPSERT in SQL Server 2005
  4. how to select next n letters after one specific letter using function in sql server by giving function name,specific letter, no of letters next
  5. SQL server query to output the data shown below
  6. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  7. What is the difference between Integrated Security = True and Integrated Security = SSPI?
  8. How to use a variable for the database name in T-SQL?
  9. What is the difference between char, nchar, varchar, and nvarchar in SQL Server?
  10. Missing artifact com.microsoft.sqlserver:sqljdbc4:jar:4.0
  11. Create a view with ORDER BY clause
  12. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  13. INSERT INTO vs SELECT INTO
  14. Possible to store value of one select column and use it for the next one?
  15. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  16. How to write UTF-8 characters using bulk insert in SQL Server?
  17. How to: Create trigger for auto update modified date with SQL Server 2008
  18. How can I Insert data into SQL Server using VBNet
  19. Retrieve data from stored procedure which has multiple result sets
  20. Use of Begin / End Blocks and the Go keyword in SQL Server?
  21. How do you set autocommit in an SQL Server session?
  22. Ampersand (&) operator in a SQL Server WHERE Clause
  23. Avg of float inconsistency
  24. how to write number to word function in sql server [closed]
  25. Milliseconds wrong when converting from XML to SQL Server datetime
  26. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  27. SQL Server equivalent to Oracle’s CREATE OR REPLACE VIEW
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. Oracle sequence but then in MS SQL Server
  30. Convert float into varchar in SQL Server without scientific notation

Leave a Comment