Running Transact-SQL Script Files Using sqlcmd 

You can use sqlcmd to execute database script files, which are text files containing a mix of Transact-SQL statements, sqlcmd commands, and scripting variables. For more information about scripting variables, see Using sqlcmd with scripting variables. sqlcmd works with the statements, commands, and scripting variables in a script file in a manner very similar to how it works with statements and commands entered interactively. The main difference is that sqlcmd reads through the input file without pause instead of waiting for a user to enter the statements, commands, and scripting variables. For more information about working with sqlcmd interactively, see Running Transact-SQL Statements Interactively Using sqlcmd.

There are different ways to create database script files:

• You can interactively build and debug a set of Transact-SQL statements in Microsoft SQL Server Management Studio, and then save the contents of the Query window as a script file.
• You can create a text file containing Transact-SQL statements using a text editor such as Microsoft Notepad.

This is an example of creating a script file and running the script using sqlcmd.

Open Notepad and type the following Transact-SQL statements and sqlcmd commands:

USE AdventureWorks
GO
SELECT TOP (5) ContactID, FirstName, LastName
FROM Person.Contact
GO

Create a folder named MyFolder. Use the File/Save As menu to save the above script as a file named MyScript.sql in the folder C:\MyFolder. Run the following command from the command prompt to run the script and place the output in a file named MyOutput.txt in the same folder:

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

When you view the contents of MyOutput.txt in Notepad, you will see the following:

Changed database context to 'AdventureWorks'.
ContactID   FirstName   LastName
----------- ----------- -----------
          1 Syed        Abbas                                             
          2 Catherine   Abel                                              
          3 Kim         Abercrombie                                       
          4 Humberto    Acevedo                                           
          5 Pilar       Ackerman                                          

(5 rows affected)

Running the sqlcmd Utility
Running Transact-SQL Statements Interactively Using sqlcmd

sqlcmd Utility

Getting SQL Server 2005 Assistance