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...

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

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
880202427
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
40.1k361549819
accept rate: 34%

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;

Version 12 adds CREATE OR REPLACE VARIABLE.

permanent link

answered 15 Sep '10, 13:23

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
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

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:

×6

question asked: 14 Sep '10, 20:07

question was seen: 2,117 times

last updated: 15 Sep '10, 13:23