Null Values In Unload Select Statement Results

[former_SQLA_member1694868]

So you have this table:

BEGIN
CREATE TABLE ATB
( ATBID INTEGER,
NAME VARCHAR (200),
SETUP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (ATBID));
INSERT INTO ATB (ATBID,NAME) VALUES (1,'NAME1');
INSERT INTO ATB (ATBID,NAME) VALUES (2,null);
INSERT INTO ATB (ATBID,NAME) VALUES (3,'NAME3');
END

From Sybase Central if you right-click the second of the rows and "Generate > Insert Statement," you get this statement:

INSERT INTO "Owner"."ATB" ("ATBID","NAME","SETUP") 
VALUES(2,NULL,'2010-11-04 10:13:45.805')

However, if you do this instead:

begin
declare @ul long varchar;
unload select * from ATB where ATBID = 2 into variable @ul;
select @ul;
end

You get this as the contents of the variable:

2,,2010-11-04 10:13:45.805

I'm guessing that because the insert statement is obviously going to another table, NULL has to be spelled out, whereas the unload is more often going to a file such as .csv where it would not be beneficial to spell out null. I'm thinking a program reading a .csv such as Excel would treat the spelled out NULL as the text 'NULL' instead of the NULL value and that's not good.

However I think it should be an option on the unload statement similar to QUOTES OFF/ON where you could specify NULLS OFF/ON with the default being off, but if you wanted them you could have them.

EDIT to Answer Mark

My use case would be that each of our customers have the same basic table structure.

So Customer A called and asked me to set something up for them that involves records in 2 existing tables in a parent-child relationship like an invoice - invoice details, or a vendor - vendor details but more generic, like a template for them to reference.
I like what I setup for them and would like to be able to insert it on the fly for other customers now that I have it set up once. However, the ID will not necessarily be in line with Customer B's database so I have to edit some things.

I thought I would write a procedure that basically exported the information formatted as insert statements I could use on Customer B's system without edits for this thing I setup and any future things I setup.

Using lots of replaces and string edits and dynamic command creation I have it just about done, except for putting quotes around the Timestamps. As I was doing it I thought that my replace ,, with ,null, and then replace ,, with , could be avoided if there was an option for unloading with nulls.

I'm still kind of stuck on the timestamp quotes, but that's the last hurdle.

[MarkCulp]

Your "guessing" and "thinking" points are correct. The current representation of a null value in the unloaded text file is nothing - i.e. the presence of no characters - as opposed to an empty string which is represented as ''. Can you give us a use-case of when (or why) you need to have the null value spelled out instead of being left as empty?

[VolkerBarth]

@Mark: Does the OUTPUT_NULLS option have an influence here, too, or is it just for the (similar) OUTPUT statement? (The reason this option already exists might be enough of a use-case...)

[former_SQLA_member1694868]

Edited to reflect use case.

[former_SQLA_member1694868 · Accepted solution]

After a palm to the forehead moment and some consultations the answer here is really:

"Don't cycle through the results of an UNLOAD statement trying to turn it into an insert statement but instead use the LOAD statement with the results of an UNLOAD."

Of course.

I'm not sure why it would be used in the future now, but it still seems to make sense that you might want the output to include nulls for some other reason. Of course then the LOAD statement would also have to be able to handle either type of input.

What a job you have Mark. Trying to cater to the whims of those who don't truly understand the whims themselves. It's appreciated.

[MarkCulp · Accepted solution]

@Siger: You are not alone. I find that 80% of computer programming is thoroughly understanding the problem.