CODEWITHSUNDEEP
sqlsql-server-2008
ken lacoste
ken lacoste

Reputation: 894

sql insert using select, case and subquery inside

select * into #transacTbl from tmpTrans
insert
   select
       (case when tmpT.TranStatus = 10
           then(
                select ID, 'Returned')
           else(
                select ID, 'GoodSale')
           end)
   from
   (
       select * from MainTransacSource
    ) as tmpT

I want to be able to insert the details of a transaction into a different table with a label if it is a returned or good sale/transaction. I did this to avoid the cursor so please avoid giving a solution using a cursor.

I know the code looks good but what I'm experiencing is that, the case statement only returns one value via subquery.

This is a simplified version of the code; I have at least 6 types of cases and should be able to insert by ROW. I hate to think that I have to repeat each case per column because the actual number of columns is about 38.

You may suggest another work-around if this doesn't fit the logic. Of course, without a cursor.

Upvotes: 1

Views: 1941

Answers (1)

d'alar'cop
d'alar'cop

Reputation: 2365

Without access to your tables and not knowing more about what precisely you want to acheive, try something like this:

select * into #transacTbl from tmpTrans
insert
   select tmpT.ID,
       (case when tmpT.TranStatus = 10
        then 'Returned'
        else 'GoodSale'
        end)
   from
      (select * from MainTransacSource) as tmpT <OR simply MainTransacSource tmpT (maybe)>

Cheers.

Upvotes: 3

Related Questions