This code works great in ISQL... version 9.0.2
But this code doesn't work at all...
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
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.
answered 14 Sep '10, 20:54
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
Version 12 adds CREATE OR REPLACE VARIABLE.
answered 15 Sep '10, 13:23