Execute multiple scripts in SQL Server using sqlcmd

Source: Internet
Author: User

Overview:

As a DBA, it is often necessary to update the official database with the SQL script provided by the developer, but a more reasonable development process, when the script is submitted to the DBA, there may already be hundreds of SQL files, and there is a sequence of execution, such as the company I am working on, more than 10 customers, one library per customer, But the database structure, stored procedures, views, and so on are exactly the same, each execution of the script (hereinafter referred to as the upgrade), if there are 100 of scripts, then the order is executed thousands of times, this workload is not a person can afford.

Workaround:

There are several ways to deal with this situation:

1, purchase third-party software (generally estimated that few people buy)

2, self-programming a small software to execute, but this logic requirements are relatively high, and the ability to program a certain level, this I do not.

3, using the method described in this article, as to what is, then look:

Use SQLCMD to execute multiple scripts on SQL Server:

Sqlcmd: Use the sqlcmd utility, which can be used at a command prompt, in the Query Editor in SQLCMD mode, in a Windows script file, or in the operating system of a SQL Server agent job (Cmd.exe ), enter Transact-SQL statements, system procedures, and script files in the job step. This utility uses ODBC to perform Transact-SQL batch processing. Detailed syntax (from MSDN) can be found on the web and is not posted here.

Sqlcmd has a very important command:: R, remember that SQLCMD is case-sensitive. When: R discovery is running SQL script, it tells Sqlcmd to put the file referenced in the file into the calling script. This will tell you to stop the current single query. and re-adjust the query, put the query should be associated in the appropriate location. In addition, using the: R command executes multiple scripts in a batch, allowing you to define a separate set of variables to contain all the scripts, but not the go terminator. The introduction of SQLCMD since 2005 can be used to replace the osql tool in the future. If you are unfamiliar with sqlcmd, you can think of it as a command-line tool that can execute T-SQL commands and scripts from the operating system.

In the following example, create 5 SQL files that have an associated effect on the TestDB database. The first script, called Create_db.sql, is used to create a database called TestDB. This script contains 4 other scripts (using the: R command. ), which is used to generate additional tables, table inserts, index creation, and the creation of stored procedures. A. bat file is used to create a sqlcmd command to execute.

The first step: Create a folder under the C drive: C:\Scripts. Then store the script in this folder:

Script 1:create_db.sql

[SQL]View Plaincopyprint?
  1. /* SCRIPT:CREATE_DB.sql */
  2. /* Create TestDB Database */
  3. --This is the main caller for each script
  4. SET NOCOUNT on
  5. GO
  6. PRINT ' Start creating TestDB database '
  7. IF EXISTS (SELECT 1 from SYS. DATABASES WHERE NAME = ' TestDB ')
  8. DROP DATABASE TestDB
  9. GO
  10. CREATE DATABASE TestDB
  11. GO
  12. : OnError exit
  13. : R c:\Scripts\CREATE_TABLES.sql
  14. : R c:\Scripts\TABLE_INSERTS.sql
  15. : R c:\Scripts\CREATE_INDEXES.sql
  16. : R c:\Scripts\CREATE_PROCEDURES.sql
  17. PRINT ' Create complete '
  18. GO

Script 2:create_indexes.sql

[SQL]View Plaincopyprint?
  1. /* Create an index */
  2. PRINT ' Start index creation '
  3. GO
  4. Use TestDB
  5. GO
  6. IF not EXISTS ( SELECT 1
  7. From SYS. INDEXES
  8. WHERE NAME = ' ix_employee_lastname ')
  9. CREATE INDEX ix_employee_lastname on DBO. EMPLOYEE (LASTNAME, FIRSTNAME)
  10. GO
  11. IF not EXISTS ( SELECT 1
  12. From SYS. INDEXES
  13. WHERE NAME = ' Ix_timecard_employeeid ')
  14. CREATE INDEX ix_timecard_employeeid on DBO. TIMECARD (EMPLOYEEID)
  15. GO


Script 3:create_procedures.sql

[SQL]View Plaincopyprint?
  1. /* Create a stored procedure */
  2. PRINT ' Creating stored procedure '
  3. GO
  4. Use TestDB
  5. GO
  6. IF object_id (' get_employee_timecards ') is not NULL
  7. DROP PROCEDURE DBO. Get_employee_timecards
  8. GO
  9. CREATE PROCEDURE DBO. Get_employee_timecards @EMPLOYEEID INT
  10. As
  11. SET NOCOUNT on
  12. SELECT *
  13. From DBO. EMPLOYEE E
  14. JOIN DBO. TIMECARD T on e.employeeid = T.employeeid
  15. WHERE E.employeeid = @EMPLOYEEID
  16. ORDER by dateworked
  17. GO


Script 4:create_tables.sql

[SQL]View Plaincopyprint?
  1. /* Create data Table */
  2. PRINT ' creating data table '
  3. GO
  4. Use TestDB
  5. GO
  6. IF object_id (' EMPLOYEE ') is not NULL
  7. DROP TABLE DBO. EMPLOYEE
  8. GO
  9. CREATE TABLE DBO. EMPLOYEE
  10. (
  11. EMPLOYEEID INT IDENTITY (1, 1)
  12. not NULL
  13. PRIMARY KEY,
  14. FIRSTNAME VARCHAR (+),
  15. LASTNAME VARCHAR (a)
  16. )
  17. GO
  18. IF object_id (' TIMECARD ') is not NULL
  19. DROP TABLE DBO. TIMECARD
  20. GO
  21. CREATE TABLE DBO. TIMECARD
  22. (
  23. Timecardid INT IDENTITY (1, 1)
  24. not NULL
  25. PRIMARY KEY,
  26. EMPLOYEEID INT not NULL,
  27. hoursworked TINYINT not NULL,
  28. Hourlyrate Money is not NULL,
  29. dateworked DATETIME not NULL
  30. )
  31. GO
  32. DECLARE @TOTAL_TABLES INT
  33. SET @TOTAL_TABLES = 2


Script 5:table_inserts.sql

[SQL]View Plaincopyprint?
  1. /* Insert Table Data */
  2. PRINT ' total TABLES CREATED = ' + CAST (@TOTAL_TABLES as VARCHAR)
  3. GO
  4. PRINT ' inserting data to table ' EMPLOYEE '
  5. GO
  6. Use TestDB
  7. GO
  8. INSERT into DBO. EMPLOYEE
  9. (FIRSTNAME, LASTNAME)
  10. SELECT ' JOHN ',
  11. ' DOE '
  12. GO
  13. INSERT into DBO. EMPLOYEE
  14. (FIRSTNAME, LASTNAME)
  15. SELECT ' JANE ',
  16. ' DOE '
  17. GO
  18. INSERT into DBO. EMPLOYEE
  19. (FIRSTNAME, LASTNAME)
  20. SELECT ' JEFF ',
  21. ' DOE '
  22. GO


Step Two: Create a bat file Create_db.bat in the C packing directory to perform sqlcmd:

[Plain]View Plaincopyprint?
    1. Sqlcmd-e-dmaster-ic:\scripts\create_db.sql
    2. PAUSE


Step three: Execute the bat file directly under the C drive:

Double-click the file to see:

Before execution, there is no testdb:

In execution:

After execution, the created things are created:

Because the order of execution is already defined in script 1, it executes directly and executes successfully.

(go) Use sqlcmd to execute multiple scripts in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.