Now we can embed SQL queries into a batch file. But how can we pass arguments from the batch script into SQL?
This can be done using a temporary table. Temporary tables live as long as the connection to the SQL Server.
But how can we fill a temporary table with runtime data and execute the embedded SQL script without creating two separate SQL Server connections by calling OSQL.EXE twice?
The trick is that OSQL allows to use the -i and -q option at the same time whereas:
Both the query string and the SQL file will be executed using the same Server connection. Some testing shows that the query string -q will always be executed before the -I SQL file, which allows us to use a query string to set up a temporary table.
- -q specifies a query string to be executed
- -i specifies a filename with SQL syntax to be executed