having issues trying to export dates, date-time fields and time formatted fields in order to import into another Database. This gives issues as when it exports to a textfile the date field isn´t encapsulated in double quotes as per a String Character.
Can anyone assist please? Thanks in advance.
asked 19 Aug '12, 05:37
Please tell us what version of SQL expects datetime values to be surrounded by "double quotes".
Please tell us EXACTLY what you want in the output file... proved an exact example.
The SQL Anywhere OUTPUT command has some options; for example:
CREATE TABLE t ( pkey INTEGER NOT NULL PRIMARY KEY, dt TIMESTAMP NOT NULL ); INSERT t VALUES ( 1, CURRENT TIMESTAMP ); COMMIT; -- As Justin showed, FORMAT SQL produces a very funky result, only useful for SQL Anywhere isql... SELECT * FROM t; OUTPUT TO 'c:/temp/t.sql' FORMAT SQL; /* SELECT * FROM t; input format sql; 1,'2012-08-19 17:29:20.589' END */ -- FORMAT TEXT uses 'single quotes' around datetimes by default... SELECT * FROM t; OUTPUT TO 'c:/temp/t.sql' FORMAT TEXT; /* 1,'2012-08-19 17:29:20.589' */ -- The quote character can be changed... SELECT * FROM t; OUTPUT TO 'c:/temp/t.sql' FORMAT TEXT QUOTE '"'; /* 1,"2012-08-19 17:29:20.589" */
answered 19 Aug '12, 17:40
Which tool are you using to do this exporting - if it's just from the data tab of a table view I don't think you have much control over this. In many csv definitions strings only need double quotes if the contain commas, CR, LF etc - dates shouldn't have commas so don't need quotes. (eg see RFC 4180)
If you use DBISQL and its OUTPUT statement you have much more control, including being able to force quotes.
answered 19 Aug '12, 10:01
The dbisql OUTPUT statement with format SQL is a slightly odd beast that is only really compatible with the corresponding dbisql input statement which it generates itself. Look at this example:
create table blah(dt datetime, st char(20)); insert into blah values (current timestamp, 'hj'); commit;
then run the OUTPUT statement
select * from blah; output to 'e:\\blah.sql' format sql;
the output file contains:
select * from blah; input format sql; '2012-08-19 18:25:00.484','hj' END
create a new table blah2 in dbisql
create table blah(dt datetime, st char(20));
then paste the output file contents into dbisql and edit the select to refer to blah2 and run it
select * from blah2; input format sql; '2012-08-19 18:25:00.484','hj' END
and it will load fine.
For general purpose exporting and importing you are probably better off using either OUTPUT & INPUT with the TEXT format or UNLOAD and LOAD which are database server SQL statements rather than dbisql commands.
answered 19 Aug '12, 13:39