By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Help on conditional insert statement using t-sql

P: 26
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
Share this Question
Share on Google+
2 Replies


P: 13
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
Expert 2.5K+
P: 2,878
Be careful with this statement:

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.