SELECT INTO with CASE statement

Question

Is there a way to do a SELECT INTO that has a CASE statement in the select part? I need to do some data transformation before it goes into the new table.

Solution 1

Sure. Here, #test is being copied to #testnew, where the case statement adds a * before each item with values beginning with 'f':

create table #test(id int, item1 varchar(10), item2 varchar(10))
insert into #test values(1,'foo','bar')
, (2,'fud','fudge')
go

select t.id
, item1=case when left(item1,1)='f' then '*'+item1 else item1 end
, item2=case when left(item2,1)='f' then '*'+item2 else item2 end
into #testnew
from #test t
go

select * from #testnew;
go

drop table #test;
drop table #testnew;
go

Result:

id          item1       item2
----------- ----------- -----------
1           *foo        bar
2           *fud        *fudge

Solution 2

Yes you can:

SELECT CASE SourceField WHEN 1 THEN 'Yes' ELSE 'No' END AS SourceField
INTO DestinationTable
FROM SourceTable

Solution 3

Yes. Be sure to name the column. Also be sure you cast the value of the Case statement to an explicit type to avoid problems.

SELECT
    Cast((
        Case(ID)
            When (1) Then 'Text'
            Else NULL
        End) as varchar(50)
    ) as NewColumn
INTO #TempTable
FROM MyTable

Comments

[jonathan.cone]

You need the END keyword, right? ELSE 'No' END AS SourceField

[aF.]

indeed, AdaTheDev where's the END ? ^^