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_datetime,s2_individual_session from lop_2008_staging

if s2_sess_datetime is not null and s2_individual_session = '1'
begin
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief1,site from lop_2008_staging
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality2,s2_langu age2,gender,
exp_removal,s2_potential_relief1,site from lop_2008_staging where s2_nationality2 <> 'None'
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief2,site from lop_2008_staging where s2_potential_relief2 is not null
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief3,site from lop_2008_staging where s2_potential_relief3 is not null
end
else
begin
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,Null,Null,gender,
exp_removal,Null,site from lop_2008_staging
end


here s2_individual_session is bit type

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

Oct 16 '08 #1

jvskarthick

The condition what you checked is

if s2_sess_datetime is not null and s2_individual_session = '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_datetime DATETIME,
s2_individual_session BIT

Step - 2
--------------------
Whether the "lop_2008_staging" 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_datetime DATETIME,
s2_individual_session BIT


select @s2_sess_datetime = s2_sess_datetime, s2_individual_session = s2_individual_session from lop_2008_staging

if @s2_sess_datetime is not null and @s2_individual_session = '1'
begin
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief1,site from lop_2008_staging
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality2,s2_langu age2,gender,
exp_removal,s2_potential_relief1,site from lop_2008_staging where s2_nationality2 <> 'None'
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief2,site from lop_2008_staging where s2_potential_relief2 is not null
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,s2_nationality1,s2_langu age1,gender,
exp_removal,s2_potential_relief3,site from lop_2008_staging where s2_potential_relief3 is not null
end
else
begin
insert #intake_staging (a_number,sess_datetime,citizenship,language,gende r,
exp_rem,pot_rel,site)
select a_number,s2_sess_datetime,Null,Null,gender,
exp_removal,Null,site from lop_2008_staging
end


Reg,
- Karthick

Oct 17 '08 #2

ck9663

Be careful with this statement:

It could lead to unexpected results.Read this

-- CK

Oct 17 '08 #3