TSQL生成数据导出SQL

CREATE PROC DataExport
@TableName varchar(100)
AS
BEGIN
DECLARE @ColumnNameList nvarchar(4000)
DECLARE @ColumnValueList nvarchar(4000)
SET @ColumnNameList=''
SET @ColumnValueList=''
DECLARE @Object_id int
SELECT @Object_id=Object_id FROM sys.objects WHERE name=@TableName AND Type='U'


DECLARE MyCursor CURSOR FOR --定义游标
SELECT Name FROM sys.columns WHERE OBJECT_ID=@Object_id ORDER BY column_id   --设置游标遍历的数据集
OPEN MyCursor     --打开游标
DECLARE @Name varchar(50)
FETCH NEXT FROM MyCursor Into @Name  --移动游标，并给变量填充数据 

WHILE @@FETCH_STATUS=0
BEGIN
IF @ColumnNameList<>''
BEGIN
SET @ColumnNameList=@ColumnNameList+','
SET @ColumnValueList=@ColumnValueList+','
END
SET @ColumnNameList=@ColumnNameList+@Name
SET @ColumnValueList=@ColumnValueList+'###+ISNULL(Convert(varchar,'+@Name+'),#NULL#)+###'

FETCH NEXT FROM MyCursor INTO @Name  --移动游标，并填充变量数据
END

CLOSE MyCursor --关闭游标
DEALLOCATE MyCursor   --销毁游标

DECLARE @SQL nvarchar(4000)
SET @SQL='Select #INSERT '+@TableName+'('+@ColumnNameList+') values('+@ColumnValueList+')# From '+@TableName
SET @SQL=replace(@SQL,'#','''')

SET @SQL='SELECT ''SET IDENTITY_INSERT '+@TableName+' ON'' UNION ALL ' +@SQL+' UNION ALL  SELECT ''SET IDENTITY_INSERT '+@TableName+' OFF'''
EXEC sp_executesql @SQL

END

生成的SQL语句中，将'NULL'替换为NULL