473,320 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help on conditional insert statement using t-sql

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
2 5852
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
2,878 Expert 2GB
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

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

Similar topics

10
by: Jacek Generowicz | last post by:
Where can I find concise, clear documentation describing what one has to do in order to enable Python's internal help to be able to provide descriptions of Python keywords ? I am in a situation...
8
by: neblackcat | last post by:
Would anyone like to comment on the following idea? I was just going to offer it as a new PEP until it was suggested that I post it here for comment & consideration against PEP 308. I'm far...
28
by: Benjamin Niemann | last post by:
Hello, I've been just investigating IE conditional comments - hiding things from non-IE/Win browsers is easy, but I wanted to know, if it's possible to hide code from IE/Win browsers. I found...
9
by: pk | last post by:
Here is my problem. I want to make a webapp that will basically take the work out of finding what tool works for what situation. There are 5 factors that go into tool selection. 1)Material...
4
by: mux | last post by:
Hi I found out that the following piece of code throws an error. 1 #include "stdio.h" 2 3 int main() 4 { 5 int a,b; 6 a= 10;
2
by: estafford | last post by:
I am having trouble writing a conditional block using ASP.NET and C#. I am trying to do something like this: 1. if page is PostBack - transfer to another page 2. if not postback - connect...
9
by: Marty | last post by:
Hi, Does using the the conditional operator (?:) instead of the common "if" statement will give a performance gain in a C# .NET 2003 application (even in C# .NET 2005?). What is the advantage...
4
by: a | last post by:
I'm having trouble testing a custom object. I've tried many different approaches. One is shown below. The XML below shows the state of the object and I'm trying to test for that state, ie there...
5
by: paulo | last post by:
Can anyone please tell me how the C language interprets the following code: #include <stdio.h> int main(void) { int a = 1; int b = 10; int x = 3;
43
by: dev_cool | last post by:
Hello friends, I'm a beginner in C programming. One of my friends asked me to write a program in C.The purpose of the program is print 1 to n without any conditional statement, loop or jump. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.