Execute SQL Files Using SQLCMD

SQLCMD is a command-line utility to connect and execute SQL queries from the command prompt. Using SQLCMD we can only connect to SQL Server instances. Importantly we can run ad-hoc as well as interactive script plus automating SQL scripts execution. This utility is pre-installed with SQL Server installation. Instead of relying on SSMS for quick tasks, we can use SQLCMD utility to execute SQL queries through ad-hoc statements or .SQL files.

In this series of posts, we have learned to use SQLCMD utility and its advantages. The first post included How to connect to SQL Server using windows and SQL Server authentication.

The series of posts so far –

Connect to SQL Server Database Using SQLCMD Utility

SQLCMD Utility

1. Command-line utility to execute SQL scripts
2. Execute ad-hoc & interactive SQL script execution
3. Execute .SQL file queries on a database for automation

SQLCMD – Help Options

Before starting up with SQLCMD it is essential to understand all the options within this utility. Understanding all the options will help to explore advanced features. You can use sqlcmd -h in the command prompt to see the following figure.

In this tech-recipe post, we will review and learn to execute SQL files using SQLCMD. Moreover, saving the output log of executed SQL files for future references. Save the file with a T-SQL statement as a .SQL file. Make sure it contains a GO keyboard at the end of the script file to indicate the end of the batch statement.

1.

Prepare .SQL Script File

I have included the following T-SQL statement in the ContosoRetailDW_Info.sql file. Make sure to terminate the SQL file with GO batch termination.

USE ContosoRetailDW;

SELECT TOP (1000) [CurrencyKey]
      ,[CurrencyLabel]
      ,[CurrencyName]
      ,[CurrencyDescription]
      ,[ETLLoadID]
      ,[LoadDate]
      ,[UpdateDate]
  FROM [ContosoRetailDW].[dbo].[DimCurrency]

SELECT MAX(currencykey) as MaxCurrencyKey from [dbo].[DimCurrency];

IF NOT EXISTS (SELECT 1 FROM [ContosoRetailDW].[dbo].[DimCurrency] WHERE CurrencyLabel = N'029')
BEGIN
	INSERT INTO [dbo].[DimCurrency] VALUES ('029', 'TST','TST Dollar', 1, GETDATE(), GETDATE());
END

GO

2.

Execute .SQL File

Following command executes the file ContosoRetailDW_Info.sql placed on D: drive on an SQL Server instance using Windows authentication login. Using -i (input file) option with SQLCMD.

sqlcmd -S DESKTOP-4COH4E7\SQLSERVER2019 -E -i D:\Work\ContosoRetailDW_Info.sql

Following command executes the file ContosoRetailDW_Info.sql placed on D: drive on an SQL Server instance using SQL Server authentication login.

sqlcmd -S DESKTOP-4COH4E7\SQLSERVER2019 -U -P YourPassword -i D:\Work\ContosoRetailDW_Info.sql

3.

Save the Output to a Log File

Save the query output, we need to use the -o (output file) option with SQLCMD. Importantly, no output is printed to the standard output screen, instead the output log is sent to ContosoRetailDW_Query.log file.

sqlcmd -S DESKTOP-4COH4E7\SQLSERVER2019 -E -i D:\Work\ContosoRetailDW_Info.sql -o D:\Work\ContosoRetailDW_Query.log

Summary

In a nutshell, we have learned to prepare, execute SQL files using SQLCMD mode. Moreover, saving query execution result to a log output file for future references. If you like this post you may read through SQL Server topic for more information.

Read more and browse through more posts related to SQL Server on Tech-Recipes.

1. Connect to SQL Server Database Using SQLCMD Utility
2. How To Deploy ISPAC File & SSIS Package From Command Line
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Create Database Diagram In SQL Server SSMS
5. How To Index Computed Column In SQL Server
6. How To Use Computed Column In SQL Server