Help on conditional insert statement using t-sql

[jayjayplane]

I want to insert value into one temp table, but based on the value from another table, like:

select s2_sess_datetim e,s2_individual _session from lop_2008_stagin g

if s2_sess_datetim e is not null and s2_individual_s ession = '1'
begin
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gender,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ language1,gende r,
exp_removal,s2_ potential_relie f1,site from lop_2008_stagin g
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gender,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality2,s2_ language2,gende r,
exp_removal,s2_ potential_relie f1,site from lop_2008_stagin g where s2_nationality2 <> 'None'
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gender,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ language1,gende r,
exp_removal,s2_ potential_relie f2,site from lop_2008_stagin g where s2_potential_re lief2 is not null
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gender,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ language1,gende r,
exp_removal,s2_ potential_relie f3,site from lop_2008_stagin g where s2_potential_re lief3 is not null
end
else
begin
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gender,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,Null ,Null,gender,
exp_removal,Nul l,site from lop_2008_stagin g
end


here s2_individual_s ession is bit type

The code about is not the workable one, could someone help me on it, many thanks~~

[jvskarthick]

The condition what you checked is

if s2_sess_datetim e is not null and s2_individual_s ession = '1'

Note : Without declaring the variable not possible to check the condition directly, it's possible by using the conditional statements like IF EXISTS()

Please use the below given steps
Step - 1
-----------------------
Declare the variable like.
DECLARE
@s2_sess_dateti me DATETIME,
s2_individual_s ession BIT

Step - 2
--------------------
Whether the "lop_2008_stagi ng" is having only one value? please check it? and use the where case if possible.

-- Use the below given SQL one and check it
-----------------------------------------------------------------
DECLARE
@s2_sess_dateti me DATETIME,
s2_individual_s ession BIT


select @s2_sess_dateti me = s2_sess_datetim e, s2_individual_s ession = s2_individual_s ession from lop_2008_stagin g

if @s2_sess_dateti me is not null and @s2_individual_ session = '1'
begin
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gende r,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ langu age1,gender,
exp_removal,s2_ potential_relie f1,site from lop_2008_stagin g
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gende r,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality2,s2_ langu age2,gender,
exp_removal,s2_ potential_relie f1,site from lop_2008_stagin g where s2_nationality2 <> 'None'
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gende r,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ langu age1,gender,
exp_removal,s2_ potential_relie f2,site from lop_2008_stagin g where s2_potential_re lief2 is not null
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gende r,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,s2_n ationality1,s2_ langu age1,gender,
exp_removal,s2_ potential_relie f3,site from lop_2008_stagin g where s2_potential_re lief3 is not null
end
else
begin
insert #intake_staging (a_number,sess_ datetime,citize nship,language, gende r,
exp_rem,pot_rel ,site)
select a_number,s2_ses s_datetime,Null ,Null,gender,
exp_removal,Nul l,site from lop_2008_stagin g
end


Reg,
- Karthick

[ck9663]

Be careful with this statement:

select @s2_sess_datetime = s2_sess_datetime, s2_individual_session = s2_individual_session from lop_2008_staging

It could lead to unexpected results.Read this

-- CK