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

Why are there quotes around the timestamp in the insert statement and not on the unload select statement results? If the data is of a type that requires quotes I would think it would always require quotes.

asked 04 Nov '10, 15:28

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%


The answer to this question is similar to the answer to your other question - the representation used in the unloaded text file is different from that which is used by SQL.

SQL is a complex language which needs to follow specific lexical parsing specifications and therefore strings such as timestamps need to be quoted so they are not confused with arithmetic expressions (e.g. '2010-11-04' is a string, 2010-11-04 is an expression equal to 1995).

In the case of unloaded text the rules are much simpler and have a different set of goals and requirements: columns are separated by commas, the domain of all column values are known (from the table specification) and column values only need to be quoted if they contain embedded characters which could confuse the parser. The overall goal is to reduce the size of the unloaded file.

permanent link

answered 04 Nov '10, 16:14

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 41%

@Mark: Does QUOTES ON work for "UNLOAD...INTO VARIABLE", too, or is it just meant (as the QUOTE clause) for "UNLOAD ..TO fileName FORMAT TEXT"?

(04 Nov '10, 16:21) Volker Barth

Hmmm, maybe I need a different question then.

(04 Nov '10, 16:51) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×36
×22
×5

question asked: 04 Nov '10, 15:28

question was seen: 1,047 times

last updated: 04 Nov '10, 16:14