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

Source: Internet
Author: User

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

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.