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.
Example of Running a Script Using sqlcmd
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)
See Also
Concepts
Running the sqlcmd Utility
Running Transact-SQL Statements Interactively Using sqlcmd