SQL Server MERGE statement with "DUAL" in use section

Question

In Oracle (or DB2, HSQLDB) I can express things like this:

MERGE INTO [target_table] USING (SELECT 1 FROM dual) ON [target_table.id = 5] WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... 

This just checks to see if there is an entry with id = 5 in target_table . If there is, then the record is updated, if not, then it is inserted. This is about the same as MySQL being more concise

 INSERT INTO [target_table] ... ON DUPLICATE KEY UPDATE ... 

How to do this in SQL Server? According to the documentation, [table_source] should be any of the following:

 <table_source> ::= { table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] [ WITH ( table_hint [ [ , ]...n ] ) ] | rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [ ,...n ] ) ] | user_defined_function [ [ AS ] table_alias ] | OPENXML <openxml_clause> | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | <joined_table> | <pivoted_table> | <unpivoted_table> } 

http://msdn.microsoft.com/de-de/library/bb510625.aspx

Obviously, SQL Server does not have a DUAL table, but none of them is a SELECT . What can I put as [table_source] ?

NB: I found out that I can create a dummy view

 CREATE VIEW dummy (one) AS SELECT 1; 

and put it like [table_source] . But I would like to omit the DDL statements just to execute this MERGE

Answer 1

See MERGE - see the example “C. Using MERGE to perform UPDATE and INSERT operations on a target table using a derived table source” that uses VALUES (aka Table Value Constructor ):

 MERGE INTO Target USING (VALUES (1)) AS Source (Number) ... 

Happy coding.