This code works great in ISQL... version 9.0.2

INPUT into mytable (..., ....) from myfile format ascii;

But this code doesn't work at all...

declare @starttime timestamp;
set @starttime =current timestamp;
INPUT into mytable (..., ....) from myfile format ascii;
-- more stuff goes here

The error is Syntax error near INSERT

I understand that the INSERT statement is handled by the ISQL client and not the server, but I think I need the BEGIN/END to contain the declared variables.

Any way around this?

asked 14 Sep '10, 20:07

Ron%20Hiner's gravatar image

Ron Hiner
accept rate: 9%

You have already correctly pointed out that INPUT and OUTPUT are ISQL commands, not SQL statements.

As such, they cannot be used inside SQL batches (like a BEGIN..END block), procedures and triggers. - AFAIK, they are handled before ISQL sends the SQL statements to the server.

You will have to use LOAD TABLE inside a SQL batch to load data from files. Note, however, that in ASA 9, LOAD TABLE can only load files that are accessible from the server, i.e. the myfile file path is relative to the server executable.

Starting with SA 11, LOAD TABLE can be used with the USING CLIENT FILE clause allowing for client-side data files.

More can be found on a similar question (you might already know:) on OUTPUT here.

permanent link

answered 14 Sep '10, 20:54

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Thanks Volker... I knew when writing it that this seemed a little too familiar. My search on INPUT turned up nothing - and now I know why!

(15 Sep '10, 13:24) Ron Hiner

You could use CREATE VARIABLE to define the variables. However, they will not be dropped automatically at the end of the script, so you would need to add corresponding DROP VARIABLE statements. Also, you might want to handle the case where the script is interrupted before completion and then re-executed using the same connection. To do so, add:

if varexists('@starttime') = 0 then

create variable @starttime timestamp;

end if;


permanent link

answered 15 Sep '10, 13:23

Bruce%20Hay's gravatar image

Bruce Hay
accept rate: 48%

excellent! I'll give that a try. Thank you!

(15 Sep '10, 13:28) Ron Hiner
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 14 Sep '10, 20:07

question was seen: 904 times

last updated: 15 Sep '10, 13:23