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