批量执行SQL脚本

刺骨的言语ヽ痛彻心扉 2022-06-08 14:40 294阅读 0赞

**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