Inserting null values

iM DOING AN INSERT iNTO..SELECT STATEMENT.
Id like to know that if under the select statement the column field is null, Id like to insert NULL in the coulumn- if there is no value.

How is the select statemnt
zachvaldezAsked:
Who is Participating?
 
CoolBurn28Connect With a Mentor Commented:
CREATE TABLE tbl_tmp1(idn int,product varchar(30));
INSERT INTO tbl_tmp1 values(1,'coke');
INSERT INTO tbl_tmp1 values(2,'sprite');
INSERT INTO tbl_tmp1(idn) values(3);
INSERT INTO tbl_tmp1 values(4,'coke');
INSERT INTO tbl_tmp1(idn) values(5);
--
CREATE TABLE tbl_tmp2(idn int,product varchar(30));
--
INSERT INTO TBL_TMP2
SELECT IDN,ISNULL(PRODUCT,'')FROM tbl_tmp1;
--
SELECT * FROM tbl_tmp2;

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
if the select returns a NULL, a NULL will be inserted in the new table.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can still select the column by name, if the value is null, indeed a NULL will be inserted.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
zachvaldezAuthor Commented:
so by default it places NULL not  empty string or ''
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly.
0
 
zachvaldezAuthor Commented:
If I do in the selct part...

Select Tid,uId, '70' as K_code,tdate from tbal where Tid = 3

Id like to insert Null if '70' is null
0
 
Éric MoreauSenior .Net ConsultantCommented:
if the select returns a null, the resulting table will have a null

if you want to replace empty values with null, you can do something like this:
insert into ...
select field1, field2, case field3 when '' then null else field3 end
from ...
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Id like to insert Null if '70' is null

'70' is a constant and it is never null! it always have the value '70'
0
 
zachvaldezAuthor Commented:
Oh, It's not my intention to place a constant value there. I got wrong logic
So Id like to change that logic then
that if  field dtCode is not null insert '70' as K_code  else null.
0
 
Éric MoreauSenior .Net ConsultantCommented:
insert into ...
select field1, field2, case when dtCode is not null then '70' else null end
from ...
0
 
karthik_a1Commented:
Use NVL function in the select statement then it will be possible enter 70 when NULL comes in the table
0
 
Éric MoreauSenior .Net ConsultantCommented:
isn't NVL an Oracle function? The question here is about MS SQL Server 2005
0
 
zachvaldezAuthor Commented:
isnull works!
0
All Courses

From novice to tech pro — start learning today.