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.

asked 28 Jan '14, 19:51

spc's gravatar image

spc
1699918
accept rate: 0%


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.

permanent link

answered 28 Jan '14, 20:42

Mark%20Culp's gravatar image

Mark Culp
22.5k9129265
accept rate: 40%

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 ',';

(29 Jan '14, 13:43) spc
Replies hidden
2

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;
(29 Jan '14, 13:48) Mark Culp
1

Worked perfect. Thanks much

(29 Jan '14, 14:34) spc
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:

×102
×5

question asked: 28 Jan '14, 19:51

question was seen: 549 times

last updated: 29 Jan '14, 14:34