May 282015
 

How many times have you had it when building a script and you build a normal INSERT statement using VALUES

DECLARE @SomeRecords TABLE (SomeRecordName VARCHAR(100))

INSERT INTO @SomeRecords (SomeRecordName)
VALUES
	('Record1')
	,('Record2')

Then suddenly you need to do some calculation for a new column that is needed

DECLARE @SomeRecords TABLE (
	SomeRecordName VARCHAR(100)
	,SomeNeededColumn int
)

Normally you would need to either do it in 2 steps with an INSERT then an UPDATE

INSERT INTO @SomeRecords (SomeRecordName)
VALUES
	('Record1')
	,('Record2')

UPDATE @SomeRecords
SET SomeNeededColumn = CASE WHEN /*some calculation*/ THEN 1 ELSE 0 END

Or rewrite it to use SELECT and UNION/UNION ALL

INSERT INTO @SomeRecords (SomeRecordName,SomeNeededColumn)
SELECT SomeRecords.SomeRecordName
	,CASE WHEN /*some calculation*/ THEN 1 ELSE 0 END AS SomeNeededColumn
FROM (
	SELECT 'Record1' as SomeRecordName UNION
	SELECT 'Record2' 
	) SomeRecords

But, you can actually do this with almost no rework at all by putting it in parentheses and adding a (ColumnName) after the normal table alias

INSERT INTO @SomeRecords (SomeRecordName,SomeNeededColumn)
SELECT SomeRecords.SomeRecordName
	,CASE WHEN /*some calculation*/ THEN 1 ELSE 0 END AS SomeNeededColumn
FROM (
	VALUES ('Record1')
		,('Record2')
	) SomeRecords(SomeRecordName)
 Posted by at 9:15 pm

  One Response to “Using VALUES in a SELECT”

  1. Very cool, Nate! Thanks for figuring this out.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)