Shulou(Shulou.com)06/03 Report--

This article is about how to implement batch execution of Sql files in Windows environment. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Prepare the bat file:

1.1.The ExecSql.bat (execute script) (file code: ANSI, which is different from the utf8 and gb2312 mentioned below. This is the default code for editing with notepad, so it is not necessary to notepad++)

@ ECHO OFFSET dbhost=127.0.0.1SET dbuser=saSET dbpasswd=saSET dbName=ApplicationREM the following is not recommended to modify the path where REM executes the script, here is the current path SET sqlpath=%~dp0REM log file name SET temp=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%.logSET logFileName=%sqlpath%%temp: = 0% SET temp=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%.logSET logFileName=%sqlpath%%temp: execute sql script echo starts execution of sql script All logs are recorded on% logFileName%for / r% I in (* .sql) do (echo% I executed & echo -% i log start-> >% logFileName% & sqlcmd-S% dbhost%-U% dbuser%-P% dbpasswd%-d% dbName%-I% I-k-b-m 10 > >% logFileName% & echo -% i log end->% logFileName% & @ echo. > > logFileName%) ECHO completed! PAUSE

ShowOrder.bat (see what scripts will be executed and in what order) (file encoding: ANSI)

@ ECHO OFFfor / r% I in (* .sql) do (echo% I) ECHO complete! PAUSE

2. Copy the ExecSql.bat to the target root directory with the file directory structure (the folder path is preferably not in Chinese), as shown in the figure. Suppose I need to execute all the sql files under this folder.

3. [optional] sort. If there is a requirement for the execution order of the sql file, you need to rename the file, as shown in the figure above. The execution order is the positive order of the file name. It is recommended to use "01." This is used as a file name prefix

Copy the ShowOrder.bat to the target root directory and double-click to run it to see if the execution order is as expected

4. [important] Encoding conversion. Generally speaking, the encoding of .sql file is utf8. If Chinese is used in the content of .sql file (comments are not counted), you need to modify the file encoding to GB2312, otherwise the wrong sql statement will be executed.

For example, the coding of 01.XXX.sql is utf8, but the data inserted into the database is garbled.

Choose between 4.1 and 4.2 below

4.1. the method to modify the encoding is to use Notepad to open the .sql file, ctrl+A (select all), ctrl+C (copy), click Notepad "Encoding"-> "coded character set"-> "Chinese"-> "GB2312", there should be a "unable to recover warning", click "Yes", ctrl+A (select all), ctrl+V (paste), and finally save.

Use the tool (EncodingConverter) to modify in batches: download the tool and unzip it to run it. Double-click BatchConvertor.exe, and set it as shown in the following figure.

After the conversion is completed, it will be executed again and you will get the correct Chinese.

5. Modify the bat file, open the bat file with Notepad++ or another editor, modify the database connection information in ExecSql.bat, and change only four variables related to connecting to the database. The rest do not need to be modified. Save after the change.

6. Double-click the bat file, and you will have the output shown in the figure.

7. Check the log and check every .log file. No news is good news. A .log file that executes the correct script usually looks like this.

If there are errors, they will be reported back. If the script is executed again, it will report errors.

Special reminder: this bat file will check all .sql files under the current folder and execute them. When you execute bat again, you need to delete some .sql files that do not need to be executed again.

Second, the principle of both sqlcmd and osql commands can execute sql files, the two parameter configuration items are almost exactly the same, here sqlcmd is chosen because the log output is relatively clean, and Microsoft intends to delete osql in the future SqlServer (no longer supported). Both commands can use XXX/? in cmd To ask for help.

Official document of sqlcmd: https://docs.microsoft.com/zh-cn/sql/tools/sqlcmd-utility?view=sql-server-ver15

Official document of osql: https://docs.microsoft.com/zh-cn/sql/tools/osql-utility?view=sql-server-ver15

Thank you for reading! On "how to achieve batch execution of Sql files in the Windows environment" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.