Question Re: Automating INPUT INTO in Technology Q&A

Automating INPUT INTO

Former Member — Tue, 28 Jan 2014 19:51:54 GMT

SQL Anywhere 12.0.1

Is it possible to import data from file by using a scheduled Stored Procedure or Event? I read in the manual that there are certain SQL statements that are not allowed in Procedures and Events, with INPUT being one of them. But there must be some way to trigger the INPUT.

Re: Automating INPUT INTO

MarkCulp — Tue, 28 Jan 2014 20:42:09 GMT

The INPUT statement is a dbisql[c] statement and cannot be used within the database server.

But there are lots of ways of getting data into the database from a procedure or event: use LOAD TABLE ... FROM FILE, xp_read_file, openxml( from file ... ), or even using proxy 'directory' tables. The exact method that you use will be dependent on the type of data that you want to import into the database. If you were using INPUT from the client then I'd recommend that you start by looking at LOAD TABLE since it is the most similar to INPUT.

Re: Automating INPUT INTO

Former Member — Wed, 29 Jan 2014 13:43:10 GMT

LOAD TABLE worked well from the procedure. New problem is that in the table I am loading into I have created an autoinc pk field that is not in the source file I am loading from. When I load the file in my pk field doesn't apply the autoinc sequence. I turned off the autoinc to see what it was trying to populate and that field is getting a '0' for each row. Below is the script.

create table Data_Incoming ( PK bigint not null default autoincrement, a long varchar, b long varchar, c long varchar, d long varchar, e double, f long varchar, g long varchar, constraint PK_IMPORT primary key (PK) );

GRANT ALL ON Data_Incoming TO dba WITH GRANT OPTION;
COMMIT;

LOAD TABLE Data_Incoming 
( a,
 b,
 c,
 d,
 e,
 g

) FROM 'C:\\directory\\File.csv' SKIP 1 FORMAT TEXT DELIMITED BY ',';

Re: Automating INPUT INTO

MarkCulp — Wed, 29 Jan 2014 13:48:59 GMT

Try loading your data into a temporary table and then inserting the data from the temp table into your permanent table. Don't include the autoincrement column when doing the insert. E.g.

create table mytab( i int default autoincrement, s long varchar );

create temporary table temptab( s long varchar );
load table temptab( s ) from 'c:\\somefilename.txt' ...other-options...;

insert into mytab( s ) select s from temptab;

Re: Automating INPUT INTO

Former Member — Wed, 29 Jan 2014 14:34:47 GMT

Worked perfect. Thanks much