How to use locally declared variables in SQL SELECT INSERT INTO?

Question

I am working on a project that involves retrieving data from one table, and parsing out some of the information from certain fields and placing them into other fields on another table. This involves some LEFT, SUBSTRING and RIGHT functions.

My issue is when I run my Query, the variables return the last value of the source table and place that as the value for each row in the fields in the target table. However, the other fields return different values for each row, which is what is desired.

SQL version is MSSQL Server 2012

Here is the code (table and field names have been changed for brevity and clarity)

USE [databaseName]
DECLARE @Year varchar(5);
DECLARE @Type varchar(2);
DECLARE @CaseType varchar(20);
DECLARE @ParsedHeader varchar(20);

SELECT @Year=SUBSTRING([tableSource].[Header],2,5)
FROM [tableSource];

SELECT @Type=LEFT([tableSource].[Header],2)
FROM [tableSource];

SELECT @CaseType=CASE(WHEN @Type='AA' THEN 'Type AA'
                      WHEN @Type='BB' THEN 'Type BB'
                      WHEN @Type='CC' THEN 'Type CC'
                      )
                      END;

SELECT @ParsedHeader =LEFT([tableSource].[OtherHeader],2)
                      +' '
                      +SUBSTRING([tableSource].[OtherHeader],3,7)
                      +'-'
                      RIGHT([tableSource].[OtherHeader],3)
FROM [tableSource];

INSERT INTO [tableTarget](
Name,
Foo,
Bar,
Year,
Type,
CaseType,
ParsedHeader)

SELECT(
Name,
Foo,
Bar,
@Year,
@Type,
@CaseType,
@ParsedHeader
)
FROM [tableSource];

When run, I get a set of results where the Year, Type, CaseType and ParsedHeader (which can be NULL, and the last record is NULL) fields are always pulled from the last record in the sourceTable. I am obviously missing something in my logic here. Any help is appreciated.

Answer 1

You have to place everything in the SELECT clause of the query used in the INSERT operation:

INSERT INTO [tableTarget](Name, Foo, Bar, Year, Type, CaseType, ParsedHeader)
SELECT Name, Foo, Bar,
       SUBSTRING([tableSource].[Header],2,5), -- @Year
       LEFT([tableSource].[Header],2),        -- @Type
       CASE 
         WHEN @Type='AA' THEN 'Type AA'
         WHEN @Type='BB' THEN 'Type BB'
         WHEN @Type='CC' THEN 'Type CC'             
       END,  -- @CaseType
       LEFT([tableSource].[OtherHeader],2)
       +' '
       + SUBSTRING([tableSource].[OtherHeader],3,7)
       +'-'
       + RIGHT([tableSource].[OtherHeader], 3) --   @ParsedHeader
FROM [tableSource];