So you have this table:
From Sybase Central if you right-click the second of the rows and "Generate > Insert Statement," you get this statement:
However, if you do this instead:
You get this as the contents of the variable:
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 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. |
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?
@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...)
Edited to reflect use case.