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