你要执行的sql语句保存为一个.sql的脚本文件(纯文本文件), 例如保存为c:\a.sql
然后写批处理调用SQL脚本文件就可以了
osql -U 帐号 -P 密码 -S localhost -i c:\a.sql
--------------------------------------------------------------------------------
如果只是简单的一句sql代码, 还可以使用(不能换行)
osql -U 帐号 -P 密码 -S localhost -Q "select * from sysobjects"
1. Restore.bat文件内容:
osql -E -S -i C:\TempDB\Restore.txt
2. Restore.txt文件内容:
use master
if exists (select * from sysdevices where name='TruckDB')
EXEC sp_dropdevice 'TruckDB'
Else
EXEC sp_addumpdevice 'disk','TruckDB', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TruckDB.mdf'
restore database TruckDB
from disk='c:\TempDB\TruckDB'
with replace
导出数据到txt:
/*导入*/
EXEC master..xp_cmdshell 'bcp "test..mintest" in d:\mintest2.txt -c -Sd02 -Usa -P'
/*导出*/
EXEC master..xp_cmdshell 'bcp "test..min" out d:\mintest2.txt -c -Sd02 -Usa -P'
osql -E -S -i C:\TempDB\Restore.txt
2. Restore.txt文件内容:
use master
if exists (select * from sysdevices where name='TruckDB')
EXEC sp_dropdevice 'TruckDB'
Else
EXEC sp_addumpdevice 'disk','TruckDB', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TruckDB.mdf'
restore database TruckDB
from disk='c:\TempDB\TruckDB'
with replace
导出数据到txt:
/*导入*/
EXEC master..xp_cmdshell 'bcp "test..mintest" in d:\mintest2.txt -c -Sd02 -Usa -P'
/*导出*/
EXEC master..xp_cmdshell 'bcp "test..min" out d:\mintest2.txt -c -Sd02 -Usa -P'
批处理执行SQL文件 |
[ 2006-3-6 16:01:29 | By: 西湖听雨 ]
|
@echo off rem --------------------------------------------- rem 设置变量,下面的这些变量要根据不同的系统分别配置。 rem 下面的这些设置在上级目录的 run.bat 文件中定义。 rem --------------------------------------------- rem set dbusername=sa rem set dbpassword=*** rem set dbserver=*** rem set dbname=**** rem ------------------------------- rem 先删除数据 rem ------------------------------- set dbusername=sa set dbpassword=12903 set dbserver=localhost set dbname=LGCM rem ------------------------------- rem 建立数据结构 rem ------------------------------- isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i LGLGX_DATA_STRUCTURE.sql -b rem ------------------------------- rem bcp导入数据 rem ------------------------------- bcp "%dbname%..LGLGX" in DATA_LGLGX.txt -S %dbserver% -U %dbusername% -P %dbpassword% -c -q rem ------------------------------- rem 更新数据库 rem ------------------------------- isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i modify_person_tbl.sql -b isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i modify_person_vw.sql -b isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i update_household_relation.sql -b isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i insert_LGLGX_household.sql -b isql -S %dbserver% -U %dbusername% -P %dbpassword% -d %dbname% -h-1 -w 80000 -i insert_LGLGX_person.sql -b @if %ERRORLEVEL% GTR 0 (goto ErrorHandel) else (goto ImportData) :ErrorHandel :ImportData pause |
转载于:https://blog.51cto.com/officevba/243596