SQL Server 查询结果转换为SQL Insert语句

创建存储过程

?

CREATE PROC [dbo].[sp_Data2InsertSQL]

@TableName AS VARCHAR(100)

AS

 

DECLARE xCursor CURSOR FOR

 

SELECT name,xusertype

FROM syscolumns

WHERE (id = OBJECT_ID(@TableName))

 

DECLARE @F1 VARCHAR(100)

DECLARE @F2 integer

DECLARE @SQL varchar(8000)

 

SET @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''

 

OPEN xCursor

FETCH xCursor INTO @F1,@F2

WHILE @@FETCH_STATUS = 0

 

BEGIN

SET @sql = @sql + ''

+ CASE WHEN @F2 IN (35,58,99,167,175,231,239,61) then ' + CASE WHEN ' +

@F1 + ' IS NULL THEN '''' ELSE '''''''' END + ' ELSE '+' END

+ 'REPLACE(ISNULL(CAST(' + @F1 + ' AS VARCHAR(8000)),''NULL''),'''''''','''''''''''')'

+ CASE WHEN @F2 IN (35,58,99,167,175,231,239,61) THEN ' + CASE WHEN ' +

@F1 + ' IS NULL THEN '''' ELSE '''''''' END + ' ELSE '+' END

+ CHAR(13) + ''','''

FETCH NEXT FROM xCursor INTO @F1,@F2

END

 

CLOSE xCursor

 

DEALLOCATE xCursor

 

SET @sql = LEFT(@sql,LEN(@sql) - 5) + ' + '')'' FROM ' + @TableName

 

EXEC (@sql)

GO

 

查询结果：
select * into tsysdataviewfTemp from tsysdataviewf where fDataViewCode='bms_employee' and fcreatorname='micro'

结果转换为SQL查询语句：
EXEC sp_Data2InsertSQL tsysdataviewfTemp
drop table tsysdataviewfTemp