批量生成插入数据SQL

--如果目标库是一个空库，执行下面语句

--替换其中的@SourceDatabase(源) 及@DerectionDatabase(目标) 参数

-------------------------------------------------------------------------------------------------------------------------

/*首先检查当前数据库中是否存在SQLSTRING表，如果存在则删除

*SQLSTRING表是用来记录生成SELECT INTO 语句的

*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='SQLSTRING' AND TYPE=N'U')

DROP TABLE SQLSTRING

/*创建日志表*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='EXEC_LOG' AND TYPE=N'U')

DROP TBALE EXEC_LOG

CREATE TABLE EXEC_LOG

(CMD VARCHAR(8000),

STATUS INT,

DISCRIPT VARCHAR(20),

MSG VARCHAR(255)

)

/*定义变量@SourceDatabase源库,@DerectionDatabase目标库

*并给两个变量赋初使值

*/

DECLARE @SourceDatabase varchar(255)

DECLARE @DerectionDatabase varchar(255)

SET @SourceDatabase='testSoure'

SET @DerectionDatabase='testDerection'

/*

*以下是根据源库生成SELECT INTO 语句，并将结果记录到SQLSTRING表中

*/

SELECT

'SELECT '

+STUFF(

(SELECT ','+NAME FROM SYS.COLUMNS

WHERE SYS.OBJECTS.object_id=SYS.COLUMNS.object_id

FOR XML PATH ('')

),1,1,'')

+' INTO ['+@DerectionDatabase+'].[dbo].['+NAME+']'

+' FROM '

+'['+@SourceDatabase+'].[dbo].['

+NAME

+']'

AS SQLSTRING

INTO SQLSTRING

FROM SYS.OBJECTS WHERE TYPE=N'U'

/*@CMD变量是用来记录SQLSTRING表中的一行记录的，

*即一条SELECT INTO 语句

*/

DECLARE @CMD VARCHAR(8000)

/*定义游标，循环地执和SQLSTRING表中的SELECT INTO语句,

*并将执行的日志记录到EXEC_LOG表中，以供以后查询

*/

DECLARE INSERT_CMD CURSOR FOR

SELECT SQLSTRING FROM SQLSTRING

OPEN INSERT_CMD

FETCH NEXT FROM INSERT_CMD INTO @CMD

WHILE (@@FETCH_STATUS=0)

BEGIN

DECLARE @E INT

DECLARE @MSG VARCHAR(255)

BEGIN TRY

EXEC(@CMD)

INSERT INTO [EXEC_LOG].[dbo].[EXEC_LOG]

(

CMD,

STATUS,

DISCRIPT,

MEG

)

VALUES

(

'执行命令 '+@CMD,

@ERROR,

(CASE @@ERROR WHEN 0 THEN '成功' else '失败' end),

CAST(@@ROWCOUNT AS VARCHAR(50) )+' 行受影响'

)

END TRY

BEGIN CATCH

SET @E=@@ERROR

SELECT @MSG=ERROR_MESSAGE()

INSERT INTO [EXEC_LOG]

(

CMD,

STATUS,

DISCRIPT,

MSG

)

VALUES

(

'执行命令 '+@CMD,

@E,

(CASE @E WHEN 0 THEN '成功' else '失败' end),

@MSG

)

END CATCH;

FETCH NEXT FROM INSERT_CMD INTO @CMD

END

CLOSE INSERT_CMD

DEALLOCATE INSERT_CMD

/*删除中间生成SQLSTRING表*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='SQLSTRING' AND TYPE=N'U')

DROP TABLE SQLSTRING

---------------------------------------------------------------------------------

--如果目标库已经存在表结构，则执行下面语句

---------------------------------------------------------------------------------

/*创建日志表*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='EXEC_LOG' AND TYPE=N'U')

DROP TBALE EXEC_LOG

CREATE TABLE EXEC_LOG

(CMD VARCHAR(8000),

STATUS INT,

DISCRIPT VARCHAR(20),

MSG VARCHAR(255)

)

/*首先检查当前数据库中是否存在SQLSTRING表，如果存在则删除

*SQLSTRING表是用来记录生成SELECT INTO 语句的

*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='SQLSTRING' AND TYPE=N'U')

DROP TABLE SQLSTRING

/*定义变量@SourceDatabase源库,@DerectionDatabase目标库

*并给两个变量赋初使值

*/

DECLARE @SourceDatabase varchar(255)

DECLARE @DerectionDatabase varchar(255)

SET @SourceDatabase='testSoure'

SET @DerectionDatabase='testDerection'

/*根据当前数据库中的所有用户生成INSERT INTO......SELECT语句*/

SELECT

'INSERT INTO ['+@DerectionDatabase+'].[dbo].['+NAME+'] '

+'('

+STUFF(

(SELECT ','+NAME

FROM SYS.COLUMNS

WHERE SYS.OBJECTS.object_id=SYS.COLUMNS.object_id

FOR XML PATH ('')

),1,1,'')

+')'

+' SELECT '

+STUFF(

(SELECT ','+NAME

FROM SYS.COLUMNS

WHERE SYS.OBJECTS.object_id=SYS.COLUMNS.object_id

FOR XML PATH ('')

),1,1,'')

+' FROM '

+'['+@SourceDatabase+'].[dbo].['

+NAME

+']'

AS SQLSTRING

INTO SQLSTRING

FROM SYS.OBJECTS WHERE TYPE=N'U'

/*

*@CMD变量是用来记录SQLSTRING表中的一行记录的，

*即一条SELECT INTO 语句

*/

DECLARE @CMD VARCHAR(8000)

/*

*定义游标，循环地执和SQLSTRING表中的SELECT INTO语句,

*并将执行的日志记录到EXEC_LOG表中，

以供以后查询

*/

DECLARE INSERT_CMD CURSOR FOR

SELECT SQLSTRING FROM SQLSTRING

OPEN INSERT_CMD

FETCH NEXT FROM INSERT_CMD INTO @CMD

WHILE (@@FETCH_STATUS=0)

BEGIN

DECLARE @E INT

DECLARE @MSG VARCHAR(255)

BEGIN TRY

EXEC(@CMD)

INSERT INTO [EXEC_LOG].[dbo].[EXEC_LOG]

(

CMD,

STATUS,

DISCRIPT,

MEG

)

VALUES

(

'执行命令 '+@CMD,

@ERROR,

(CASE @@ERROR WHEN 0 THEN '成功' else '失败' end),

CAST(@@ROWCOUNT AS VARCHAR(50) )+' 行受影响'

)

END TRY

BEGIN CATCH

SET @E=@@ERROR

SELECT @MSG=ERROR_MESSAGE()

INSERT INTO [EXEC_LOG]

(

CMD,

STATUS,

DISCRIPT,

MSG

)

VALUES

(

'执行命令 '+@CMD,

@E,

(CASE @E WHEN 0 THEN '成功' else '失败' end),

@MSG

)

END CATCH;

FETCH NEXT FROM INSERT_CMD INTO @CMD

END

CLOSE INSERT_CMD

DEALLOCATE INSERT_CMD

/*删除中间生成SQLSTRING表*/

IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME='SQLSTRING' AND TYPE=N'U')

DROP TABLE SQLSTRING