批量执行SQL脚本

1.先要 xp_cmdshell 这个东东给打开，打开方法如下：

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

测试就这样 exec master.dbo.xp_cmdshell 'dir d:\'

2.我们做一个分隔函数，如下：

ALTER FUNCTION  [dbo].[f_split]
(
     @SourceSql VARCHAR(MAX),--字符串
     @StrSeprate VARCHAR(10)--分隔符
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @i INT
    SET @SourceSql=RTRIM(LTRIM(@SourceSql))
    SET @i=CHARINDEX(@StrSeprate,@SourceSql)
    WHILE @i>=1
    BEGIN
        SET @SourceSql=SUBSTRING(@SourceSql,@i+1,LEN(@SourceSql)-@i)
        SET @i=CHARINDEX(@StrSeprate,@SourceSql)
    END
    RETURN @SourceSql
END

测试 ：

SourceSql='2017-09-28    123.sql'
StrSeprate =' '  是空格不是空 

返回：123.sql

3.CREATE PROC 来执行操作

ALTER PROCEDURE [dbo].[Usp_BatchImport]
 (
 @returnCode INT=0 OUTPUT, --返回错误数
 @returnSucceed INT=0 OUTPUT, --成功执行文件数量
 @serverName VARCHAR(1000), --服务器名称
 @dataBase   VARCHAR(500), --数据库名称
 @uid     VARCHAR(50), --用户名
 @pwd        VARCHAR(50), --密码
 @filePath VARCHAR(5000)  --文件路径
 )
AS
BEGIN
DECLARE @strsql VARCHAR(2000)
SET @strsql=' dir '+@filePath+'\*.sql'
--把读到的文件名导到临时表中
CREATE TABLE #tmptb(fileDes VARCHAR(5000))
INSERT INTO #tmptb
EXEC master.dbo.xp_cmdshell  @strsql
--有木有错误
IF(@@ERROR<>0)
BEGIN
    SET @returnCode=1
    RETURN @returnCode
END
--把分隔后的文件名放在另外一个表中，带有.sql的文件
SELECT dbo.f_split(fileDes,' ') AS fileDes
INTO #tmepUSE FROM #tmptb WHERE fileDes LIKE '%.sql'

DECLARE @strFileName VARCHAR(2000)
DECLARE @REEXE INT

--声明一个游标同时从#tmepUSE中给游标赋值
DECLARE FETFILENAME CURSOR FOR SELECT fileDes FROM #tmepUSE
OPEN FETFILENAME FETCH 
NEXT FROM FETFILENAME INTO @strFileName

WHILE @@FETCH_STATUS=0
BEGIN
    --此句比较关键 
    SET @strsql='osql -S '+@serverName
      +' -U '+@uid +' -P '+@pwd +' -d '+@dataBase +' -i '
      +@filePath +'\'+  @strFileName 

    EXEC @REEXE=master.dbo.xp_cmdshell @strsql

    IF @REEXE=0
        SET @returnSucceed=@returnSucceed+1
    ELSE
    BEGIN
        SELECT @strFileName
        SET @returnCode=@returnCode+1
    END
    FETCH NEXT FROM FETFILENAME INTO @strFileName
END
CLOSE FETFILENAME
DEALLOCATE FETFILENAME  
END

测试：

declare @returnCode int 
declare @returnSucceed int 
set @returnCode=0
set @returnSucceed=0
--这是我自己的测试环境
exec [Usp_BatchImport] @returnCode output,@returnSucceed output,'WIN7-1706042132','TEMP','sa','123456','D:\SQL'
print @returnCode
print @returnSucceed