SQL Server executes. SQL script in a folder at one time

Original: [SQL Server] executes the. SQL script in the folder at once

The initial database, we developers will prepare a lot of. SQL scripts to create tables, view even initial data, how to execute the *.sql in the folder at one time or specify several. sql scripts?

To save a little bit of initializing the database for job time.

Fortunately we have an example of MSDN executing scripts using sqlcmd:

sqlcmd-s myserver\instancename-i C:\myScript.sql

If you have only a few, it is convenient to string into a bat or PowerShell, but if you have more than one,

You have to repeat the writing many times, and there is no way to handle to the specified folder. SQL needs to be executed

Quickly find the answer from the Internet, and then we rewrite the syntax and write the executed log to the specified log file name.

1. Execute the. SQL in the specified folder.

@echo offrem 资料库IP\资料库执行个体名称set dbIp=stanley\SQL2014rem 资料库名称set dbName=TSQL2014rem 登入帐号set dbUsrAcc=ImDeveloperrem 使用者密码set dbUsrPwd=Passw0rdrem 整理完毕的SQL指令集资料夹位置set batchFilePath="C:\TestDB\db_script\folder"rem 程式开始执行cd %batchFilePath%FOR /f %%i IN (‘DIR *.Sql /B‘) do call :RunScript %%iGOTO :END:RunScriptEcho Executing %1sqlcmd -S %dbIp% -d %dbName% -U %dbUsrAcc% -P %dbUsrPwd% -i %1 >>view.logEcho Completed %1:END

2. Through the list. sql File Execution

list.sql List (sql01~06 and bat files in the same directory)

:r .\sql01.sql :r .\sql02.sql :r .\sql03.sql :r .\sql04.sql :r .\sql05.sql :r C:\testDB\db_script\sql06.sql

Batch file contents

@echo offrem 资料库IP\资料库执行个体名称set dbIp=stanley\SQL2014rem 资料库名称set dbName=TSQL2014rem 登入帐号set dbUsrAcc=ImDeveloperrem 使用者密码set dbUsrPwd=Passw0rdrem 清单.SQL档放置路径set dbSqlFilePath="C:\test\db_script\list.sql"rem 程式开始执行cd %batchFilePath%sqlcmd -S %dbIp% -d %dbName% -U %dbUsrAcc% -P %dbUsrPwd% -i %dbSqlFilePath% >view.logPAUSE@echo on

Today, colleagues to ask, a whole forget how the original string, quick notes down.

Reference:

Connect to Database Engine using sqlcmd

Execute Transact-SQL script archives using sqlcmd

Run all SQL files in a directory

SQL server–executing multiple Script Files Using SQLCMD

SQL Server executes. SQL script in a folder at one time