• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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
0
zachvaldez
Asked:
zachvaldez
  • 5
  • 4
  • 2
  • +2
1 Solution
 
É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
 
zachvaldezAuthor Commented:
so by default it places NULL not  empty string or ''
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
CoolBurn28Commented:
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
 
zachvaldezAuthor Commented:
isnull works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now