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?

Answer 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.