MSSQL 导出sql数据语句

本文介绍了一个MSSQL存储过程,用于将指定表的数据导出为SQL Insert语句,便于数据迁移。该过程遍历指定表的所有列,并适当地处理不同数据类型的列,最终生成可用于插入新数据的SQL语句。

  功能使用,导出mssql数据库表中的数据,以insert 语句的格式,方便数据转移

create PROCEDURE [dbo].[outputdata]

@tableName Varchar(100)--表名
AS
DECLARE @IsIdentity INT
DECLARE @columnName VARCHAR(100)--列名
DECLARE @TypeName VARCHAR(100)--数据类型
DECLARE @columns VARCHAR(8000)--
DECLARE @columnsAndhead VARCHAR(8000)--

SET @columnsAndhead = 'INSERT INTO '+@tableName+'(['
SET @columns = ''

--获取表的所有字段名称
DECLARE a CURSOR FOR
SELECT COLUMNPROPERTY( a.id,a.name,'IsIdentity') AS IsIdentity, a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype AND b.xtype=b.xusertype
WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)

OPEN a

FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsIdentity =0
BEGIN
IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
BEGIN
SET @columns = @columns + 'ISNULL(CAST(['+@columnName +'] AS VARCHAR),''NULL'')+'',''+'
END
ELSE
BEGIN
SET @columns = @columns+ '''''''''+ ISNULL(CAST(['+@columnName +'] AS VARCHAR(50)),''NULL'')+ ''''''''+'',''+'
END
SET @columnsAndhead = @columnsAndhead + ''+ @columnName +'],['
END
FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
END


SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-2) +') VALUES('''
SELECT @columns = left(@columns,len(@columns)-5)

CLOSE a
DEALLOCATE a
print 'select '''+@columnsAndhead+'+'+@columns +'+'')'' as InsertSQL from '+@tableName
exec('select '''+@columnsAndhead+'+'+@columns +'+'')'' as InsertSQL from '+@tableName)

--END

转载于:https://www.cnblogs.com/Snowolf/archive/2011/04/12/2013864.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值