Code to execute SQL statements using BAT batch _dos/bat

1, to save the execution of SQL in a file, here is 20110224.sql.
2. Create a new batch file with an extension. bat, enter the following command and save, double-click the. bat file, and the system will automatically execute the 20110224.SQL statement:

Copy Code code as follows:

Osql-s gdjlc-d testdb-u sa-p 1-i 20110224.sql

osql parameters See below
=======================================================================
：

E:\>osql/?
Icrosoft (R) SQL Server command line tool
Version 10.0.1600.22 NT INTEL X86
Copyright (c) Microsoft Corporation. All rights reserved.

Note: osql does not support all features of SQL Server 2008.
Please use SQLCMD. For more information, see SQL Server Books Online.

Usage: osql [-u login ID] [-p password]
[-s server]           [-H host name] [-E Trusted Connection]
[-D use database name]       [-L Login Timeout] [-t query timeout value]
[-H title]         [-S column Separator] [-w Column Width]
[-A packet size]         [e-echo input] [-I allow quoted identifiers]
[-l list Servers]         [End of-C command] [-D ODBC DSN name]
[-Q "command line query"] [-Q "command line query" and exit]
[-N Delete numbering] [-m error level]
[-R message sent to STDERR] [-V Severity level]
[-I input file] [-O Output FILE]
[-P print statistics] [Abort batch with error-B]
[-x[1] Disables the command, [displays warnings while exiting]]
[-O disables the following items with the old ISQL behavior]
<EOF> Batch Processing
Automatically adjust console width
Wide Message
Default error level is-1 and 1
[-? display syntax summary]

To execute an instance of SQL command through a batch process:

To execute a SQL command by using batch processing:

1. restore.bat File content:

OSQL-E-s-i C:\TempDB\Restore.txt

2. Restore.txt file content:

Copy Code code as follows:

Use master
if exists (SELECT * from sysdevices where name= ' truckdb ')
EXEC sp_dropdevice ' truckdb '
Else
EXEC sp_addumpdevice ' disk ', ' truckdb ', ' C:\Program Files\Microsoft SQL Server\mssql\data\truckdb.mdf '
Restore Database Truckdb
From disk= ' C:\TempDB\TruckDB '
With replace

Export Data to txt:

Copy Code code as follows:

EXEC Master.. xp_cmdshell ' bcp ' test. Mintest "in D:\mintest2.txt-c-sd02-usa-p"
EXEC Master.. xp_cmdshell ' bcp ' test. Min "Out d:\mintest2.txt-c-sd02-usa-p"

In many cases, the need to update the database to the customer is not worth

Run a trip, customers do not know how to operate, remote operation speed is still not up to ... Then you can save the SQL statement to be updated into a file, write a batch in the command line

State to execute the statements in this SQL file by invoking the Query Analyzer.

The following is an explanation of the Query Analyzer (isqlw) command-line arguments in SQL Server Help:

The isqlw utility (SQL Query Analyzer) allows you to enter Transact-SQL statements, system stored procedures, and script files. By setting a shortcut or creating a batch

File, you can start a preconfigured SQL Query Analyzer.

Grammar
isqlw
[-?] |
[
[-S Server_name[instance_name]]
[-D database]
[-E] [-u user] [-p password]
[{i input_file} {-O output_file} [-F {u| a| O}]]
[F File_list]
[-C Configuration_file]
[-D Scripts_directory]
[-T Template_directory]
]

Parameters
-?

Displays usage information.

-S Server_name[instance_name]:

Specifies the Microsoft®sql server™2000 instance to which you want to connect. Specifies the server_name that is used to connect to the default instance of SQL Server 2000 on this server.

Specifies the server_nameinstance_name that is used to connect to the SQL Server 2000 named instance on this server. If no server is specified, ISQLW will connect to the local

The default instance of SQL Server on the computer. This option is required when performing isqlw from a remote computer on the network.

-D Database

When ISQLW is started, a use database statement is issued. The default value is the user's default database.

-E

Use a trusted connection without requesting a password.

-U user

User Login ID. The login ID is case-sensitive.

-p password

is the login password. The default setting is NULL.

-I. Input_file

Identifies a file that contains a batch of SQL statements or stored procedures. The-I and-o options must be specified at the same time. If you specify the-I and-o options, the query in the input file is executed and

Saves the results to the output file. The user interface is not displayed during query execution. When execution completes, the process exits.

-O output_file

Identifies the file that receives output from ISQLW. You must specify both the – I and –o options. If you specify the-I and-o options, the query in the input file is executed and the knot

The fruit is saved to the output file. The user interface is not displayed during query execution. When execution completes, the process exits. If the file format is not specified with-F, the output file uses the

The same type as the input file.

-F {u| a| O

is the format of the input file and the output file. Values include Unicode, ANSI, and OEM. If-f is not specified, automatic mode is used (if the file is marked in Unicode format, the

Open in Unicode format, otherwise open the file in ANSI format.

-F File_list

Loads the listed files into SQL Query Analyzer. With the-f option, you can load one or more files (the file name is separated by a single space). If multiple files are specified

, the files are opened with the same connection context. The file name can contain the directory path where the file resides. You can use wildcard characters, such as asterisks in C:test*.sql

(*)。

-C Configuration_file

Use the settings specified in the configuration file. Other parameters that are explicitly specified at the command prompt override the settings in the appropriate configuration file.

-D Scripts_directory

Overrides the default storage script directory specified in the registry or in the configuration file specified with –C. This value is not persisted in the registry or in the configuration file. To query analysis in SQL

To view the current value of this option, click the Tools menu, and then click Options.

-T Template_directory

Overrides the default template directory specified in the registry or in the configuration file specified with –C. This value is not persisted in the registry or in the configuration file. To be in SQL Query Analyzer

To view the current value of this option, click the Tools menu, and then click Options.

Comments

You can use the ISQLW utility when you have a user interface or no user interface. To run ISQLW without a user interface, specify valid logon information (with a trusted
Connection or valid login ID and password for SQL Server 2000 instance) and input and output files. ISQLW will execute the contents of the input file and save the results to the output file
In
If you do not specify an input and output file, isqlw runs interactively and starts SQL Query Analyzer. If a valid login information is specified, ISQLW will connect directly to SQL
Server 2000 instance. If the specified connection information is not sufficient, the Connect to SQL Server dialog box appears.
ISQLW and SQL Query Analyzer use the ODBC API. The utility uses the default settings of the SQL-92 microsoft®sql Server ODBC driver.
Example

A. Execute SQL statements
This example uses Windows authentication to connect to the pubs database on MyServer and to execute the Input.sql file. The results are saved in the Output.txt file. These
The file opens as a Unicode file.
Isqlw-s myserver-d pubs-e-I. Input.sql-o OUTPUT.TXT-FU

B. Using wildcard characters
This example loads two files into SQL Query Analyzer. will use Windows authentication to connect to the local server.
Isqlw-d pubs-e-F "C:Program FilesMicrosoft SQL servermssqlinstallinstpubs.sql" "C:Program FilesMicrosoft
SQL Servermssqlinstallinstcat.sql "

C. Loading multiple files
This example loads all. sql files into SQL Query Analyzer. All connections use Windows authentication and point to the pubs database on the local server.
Isqlw-d pubs-e-F "C:Program FilesMicrosoft SQL servermssqlinstall*.sql"

D. Using Unicode files
This example connects to the MyServer (pubs database) and executes the SQL statements in Input_file, which are stored in output_file.
Isqlw-s myserver-d pubs-u sa-p-I input_file-o output_file