Use SQLCMD to perform multiple script reprints in SQL Server

Unknown origin

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?

2. /* SCRIPT:CREATE_DB.sql */
4. /* Create TestDB Database */
8. --This is the main caller for each script
10. SET NOCOUNT on
12. GO
16. PRINT ' Start creating TestDB database '
18. IF EXISTS (SELECT 1 from SYS. DATABASES WHERE NAME = ' TestDB ')
20. DROP DATABASE TestDB
22. GO
24. CREATE DATABASE TestDB
26. GO
30. : OnError exit
34. : R c:\Scripts\CREATE_TABLES.sql
36. : R c:\Scripts\TABLE_INSERTS.sql
38. : R c:\Scripts\CREATE_INDEXES.sql
40. : R c:\Scripts\CREATE_PROCEDURES.sql
44. PRINT ' Create complete '
46. GO

Script 2:create_indexes.sql

[SQL]View Plaincopyprint?

2. /* Create an index */
4. PRINT ' Start index creation '
6. GO
8. Use TestDB
10. GO
12. IF not EXISTS ( SELECT 1
14. From SYS. INDEXES
16. WHERE NAME = ' ix_employee_lastname ')
18. CREATE INDEX ix_employee_lastname on DBO. EMPLOYEE (LASTNAME, FIRSTNAME)
20. GO
22. IF not EXISTS ( SELECT 1
24. From SYS. INDEXES
26. WHERE NAME = ' Ix_timecard_employeeid ')
28. CREATE INDEX ix_timecard_employeeid on DBO. TIMECARD (EMPLOYEEID)
30. GO

Script 3:create_procedures.sql

[SQL]View Plaincopyprint?

2. /* Create a stored procedure */
4. PRINT ' Creating stored procedure '
6. GO
8. Use TestDB
10. GO
12. IF object_id (' get_employee_timecards ') is not NULL
14. DROP PROCEDURE DBO. Get_employee_timecards
16. GO
18. CREATE PROCEDURE DBO. Get_employee_timecards @EMPLOYEEID INT
20. As
22. SET NOCOUNT on
26. SELECT *
28. From DBO. EMPLOYEE E
30. JOIN DBO. TIMECARD T on e.employeeid = T.employeeid
32. WHERE E.employeeid = @EMPLOYEEID
34. ORDER by dateworked
38. GO

Script 4:create_tables.sql

[SQL]View Plaincopyprint?

2. /* Create data Table */
4. PRINT ' creating data table '
6. GO
8. Use TestDB
10. GO
12. IF object_id (' EMPLOYEE ') is not NULL
14. DROP TABLE DBO. EMPLOYEE
16. GO
18. CREATE TABLE DBO. EMPLOYEE
20. (
22. EMPLOYEEID INT IDENTITY (1, 1)
24. not NULL
26. PRIMARY KEY,
28. FIRSTNAME VARCHAR (+),
30. LASTNAME VARCHAR (a)
32. )
34. GO
38. IF object_id (' TIMECARD ') is not NULL
40. DROP TABLE DBO. TIMECARD
42. GO
44. CREATE TABLE DBO. TIMECARD
46. (
48. Timecardid INT IDENTITY (1, 1)
50. not NULL
52. PRIMARY KEY,
54. EMPLOYEEID INT not NULL,
56. hoursworked TINYINT not NULL,
58. Hourlyrate Money is not NULL,
60. dateworked DATETIME not NULL
62. )
64. GO
68. DECLARE @TOTAL_TABLES INT
70. SET @TOTAL_TABLES = 2

Script 5:table_inserts.sql

[SQL]View Plaincopyprint?

2. /* Insert Table Data */
6. PRINT ' total TABLES CREATED = ' + CAST (@TOTAL_TABLES as VARCHAR)
8. GO
10. PRINT ' inserting data to table ' EMPLOYEE '
12. GO
14. Use TestDB
16. GO
18. INSERT into DBO. EMPLOYEE
20. (FIRSTNAME, LASTNAME)
22. SELECT ' JOHN ',
24. ' DOE '
26. GO
28. INSERT into DBO. EMPLOYEE
30. (FIRSTNAME, LASTNAME)
32. SELECT ' JANE ',
34. ' DOE '
36. GO
38. INSERT into DBO. EMPLOYEE
40. (FIRSTNAME, LASTNAME)
42. SELECT ' JEFF ',
44. ' DOE '
46. 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.

Summarize:

Depending on your experience, it's better to develop a batch execution tool that can be used in a small number of scripts.

Use SQLCMD to perform multiple script reprints in SQL Server