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)
Very cool, Nate! Thanks for figuring this out.