Using a case statement when doing an insert into

I am inserting into a table by selecting from another table. I need to set a value based upon one of the column values read. So I need to use a case statement.

See line 12 below. I need help, because syntactically line 12 is not correct. Should I instead replace line 6 with line 12? Can someone help me out please?

1  INSERT INTO smtstage..SMTS_BLLD_L2_STAGE_ASSOC_USERS(
2       ADDTOLIST,
3       P_CODE,
4      P_USER_ID,
5      SE_NUM,
6      P_Id
   
7    SELECT
8      'Y',
9     'PILT',
10      el.UserId,
11      el.Num END
12      el.P_Id = (CASE WHEN el.P_ACTIVE <> 1 then 1 ELSE el.P_ACTIVE =2)


   FROM EmployeeList el
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Duy PhamFreelance IT ConsultantCommented:
Try this:

INSERT INTO smtstage..SMTS_BLLD_L2_STAGE_ASSOC_USERS(
    ADDTOLIST,
    P_CODE,
    P_USER_ID,
    SE_NUM,
    P_Id
)

SELECT
    'Y',
    'PILT',
    el.UserId,
    el.Num,
    CASE WHEN el.P_ACTIVE <> 1 THEN 1 ELSE 2 END

FROM EmployeeList el 

Open in new window

0
 
sarabhaiCommented:
above is corrected one but also little difference
INSERT INTO smtstage..SMTS_BLLD_L2_STAGE_ASSOC_USERS(
    ADDTOLIST,
    P_CODE,
    P_USER_ID,
    SE_NUM,
    P_Id
)

SELECT
    'Y',
    'PILT',
    el.UserId,
    el.Num,
    CASE WHEN el.P_ACTIVE <> 1 THEN 1 ELSE 2 END AS el.P_Id
FROM EmployeeList el
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
First answer is correct.  When Inserting, column aliases in the SELECT clause are not required.

Also both experts caught that your T-SQL was missing the right paren ) at the end of line 6.   Also line 11 has an END that serves no purpose, and is missing a comma.

For an image and code-heavy tutorial on CASE blocks please check out my article SQL Server CASE Solutions, and if it helped you hit the big 'Was this article helpful? button at the end.

Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.