Correct syntax for Insert Into Select when using Case statements in SQL Server

Question

I am trying to insert into a temporary table from a select with a case statement defining which table to insert from but I cannot get the syntax right.

I would like to do something like the following:-

DECLARE @TempTable TABLE(ID int IDENTITY, Charges varchar(270));
INSERT INTO @TempTable SELECT (
  CASE @inItemType
    WHEN 1 THEN SELECT ID, Charges FROM TicketType WHERE Charges <> ''
    WHEN 10 THEN SELECT ID, Charges FROM SeatType WHERE Charges <> ''
  END
);

Does anyone even know if this is possible or can suggest a better way?

Damien_The_Unbeliever · Accepted Answer · 2013-07-25 09:47:36Z

1

I would just do:

DECLARE @TempTable TABLE(ID int IDENTITY, Charges varchar(270));
INSERT INTO @TempTable
SELECT ID, Charges FROM TicketType WHERE Charges <> '' and @inItemType=1
UNION ALL
SELECT ID, Charges FROM SeatType WHERE Charges <> '' and @inItemType=10;

A CASE expression, in common with most expressions in most mainstream languages, computes a single (scalar) value. It cannot return tuples or (as in this case) entire rowsets.

answered Jul 25, 2013 at 9:47

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct syntax for Insert Into Select when using Case statements in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related