Script files — You can run multiple queries or stored procedures in a single command using the file directive. The file directive takes a text file as an argument and executes all of the SQL queries and exec directives in the file as if they were entered interactively. Any show, explain, recall, or exit directives are ignored. For example, the following command processes all of the SQL queries and procedure invocations in the file
myscript.sql
:$ sqlcmd 1> file myscript.sql;
If the file contains only data definition language (DDL) statements, you can also have the entire file processed as a batch by including the
-batch
argument:$ sqlcmd 1> file -batch myscript.sql;
If a file or set of statements includes both DDL and DML statements, you can still batch process a group of DDL statements by enclosing the statements in a
file -inlinebatch
directive and the specified end marker. For example, in the following code the three CREATE PROCEDURE statements are processed as a batch:load classes myprocs.jar; file -inlinebatch END_OF_BATCH CREATE PROCEDURE FROM CLASS procs.AddEmployee; CREATE PROCEDURE FROM CLASS procs.ChangeDept; CREATE PROCEDURE FROM CLASS procs.PromoteEmployee; END_OF_BATCH
Batch processing the DDL statements has two effects:
Batch processing can significantly improve performance since all of the schema changes are processed and distributed to the cluster nodes at one time, rather than individually for each statement.
The batch operates as a transaction, succeeding or failing as a unit. If any statement fails, all of the schema changes are rolled back.
Exit — When you are done with your interactive session, enter the exit directive to end the session and return to the shell prompt.
To run a sqlcmd command without starting the interactive prompt, you can pipe the command through standard input to the sqlcmd command. For example: