Moving null data into a not null column

Question

Moving null data into a not null column

SQLDCH

SSChampion

Points: 11635
More actions
June 18, 2009 at 5:13 pm

#210979

I'm trying to pull data from a table that has nulls into a table and field that does not allow nulls, using 'insert into':
insert into myTable
select
e_id as EmpID,
n_id as NationalID,
blah blah blah
where
blah blah blah
(it's about 500k row table)
The n_id column does not allow nulls in the destination database. I talked to my Oracle guy, he told me he would DECODE on the fly. Research says that using CASE I might be able to pull this off, just having problems making it work.
I can't modify either schema, nor update the data before it's moved. Can I do this all in one statement, or will I need one for 'is not null' and one for 'is null', then merge the data?
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Yeah, well...The Dude abides.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

dh (6/18/2009)
I'm trying to pull data from a table that has nulls into a table and field that does not allow nulls, using 'insert into':
insert into myTable
select
e_id as EmpID,
n_id as NationalID,
blah blah blah
where
blah blah blah
(it's about 500k row table)
The n_id column does not allow nulls in the destination database. I talked to my Oracle guy, he told me he would DECODE on the fly. Research says that using CASE I might be able to pull this off, just having problems making it work.
I can't modify either schema, nor update the data before it's moved. Can I do this all in one statement, or will I need one for 'is not null' and one for 'is null', then merge the data?
TIA.

Just use the IsNull function:

insert into myTable

select

e_id as EmpID,

IsNull(n_id, -1) as NationalID,

blah blah blah

where

blah blah blah

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2