470,848 Members | 1,690 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,848 developers. It's quick & easy.

Null How to handle

I already asked this question; however, I am giving all the details
now:

We get large files(millions of records) and we need to load it into our
tables using import export wizard. Some of the fields in the file can
be Null and so we are forced to create table with fields that allow
Nulls with default ''. However when we insert data into these tables
it puts Null in those fields even though we have a default '' (I do not
think we have any work around for that; do we?)

Finally we need to go through each field and update it to '' if it is a
Null and that takes LOT OF TIME.
If (select count (*) from <tablename> where <columname> is Null) >0
Begin
Update <tablename>
set <columnName> = ''
where <columnName> is Null
end

Please let me know if there are any work arounds for this crisis ?
Thank you very much in advance!

Jul 23 '05 #1
1 1217
ge******@hotmail.com wrote:

I already asked this question; however, I am giving all the details
now:

We get large files(millions of records) and we need to load it into our
tables using import export wizard. Some of the fields in the file can
be Null and so we are forced to create table with fields that allow
Nulls with default ''. However when we insert data into these tables
it puts Null in those fields even though we have a default '' (I do not
think we have any work around for that; do we?)


You can eliminate NULLs in the INSERT statement. If your original
statement was:

INSERT INTO DestTable
SELECT MyString, MyNumber, MyDate
FROM ImportFile

then you could rewrite this to:

INSERT INTO DestTable
SELECT COALESCE(MyString,''), COALESCE(MyNumber,0),
COALESCE(MyDate,'19000101')
FROM ImportFile

HTH,
Gert-Jan
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Bodza Bodza | last post: by
8 posts views Thread by Manish Jain | last post: by
7 posts views Thread by Kevin Cline | last post: by
4 posts views Thread by ___Newbie___ | last post: by
4 posts views Thread by wapsiii | last post: by
6 posts views Thread by Dean Slindee | last post: by
5 posts views Thread by John | last post: by
12 posts views Thread by p.lavarre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.