Options for including literal values in scripts – syntax that I find particularly hard to remember.

The feature is named Table Value Constructor. It was introduced in 2008 – see this article.

Source of Merge statement

MERGE INTO dbo.TargetTable AS Target
USING 
    (VALUES
	(1, 'One'),
	(2, 'Two')
	) AS
	Source (MyIntCol, MyTextCol)

ON Target ...

Content of CTE

There doesn’t seem to be a more compact method than this:

WITH CTE AS (
 SELECT * FROM( VALUES
    ('ID', '1' ,'int')
   ,('DatabaseName', '0' ,'sysname')
   ,('SchemaName', '0' ,'sysname')
    ) DT (ColumnName, PrimaryKeyFlag, CondensedDataType) )
SELECT * FROM CTE

 

no comment

Sorry, comments closed.