获取数据库表中数据的insert语句
来源:互联网 发布:zip在linux下解压 编辑:程序博客网 时间:2024/05/19 02:18
/*
获取数据库表中的insert语句
**/
IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE proc spGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@sql)
end
GO
获取数据库表中的insert语句
**/
IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE proc spGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@sql)
end
GO
0 0
- 获取数据库表中数据的insert语句
- 从数据库中获取Insert语句
- Sql Server 快速获取表中数据的Insert语句,便于快速复制
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 从Oracle数据库中读取数据,自动生成INSERT语句
- 运用INSERT INTO语句向数据库中插入数据失败。
- SQL语言使用insert语句向数据库表格中插入或添加新的数据行
- 导出数据库数据记录成insert语句的存储过程
- 将数据库数据导出成insert语句的存储过程
- 将数据库表中的数据转为sql中的insert语句
- 以insert语句方式导出oracle数据库指定表名的数据
- insert语句后如何获取insert数据的主键值自动编号
- mysql insert语句后如何获取insert数据的主键值自动编号
- 把某个表的数据导出成insert语句(数据导出 insert语句)
- sqlserver 中 将数据 导出为 insert语句 的Proc
- 《ASP.NET本质论》大文件上传
- POJ 3255 Roadblocks
- OpenCV学习笔记18 OpenCV高级GUI和多媒体模块 HIGHGUI MODULE (二)
- jquery ajax 表单验证
- 申请iOS develop program
- 获取数据库表中数据的insert语句
- hdu oj Period (kmp的应用)
- 获取手机APP对网络访问数据的一个思路
- ios记住密码KeychainItemWrapper
- 移动wap input登陆框自适应屏幕大小
- JSP中JSTL核心标签库的<c:choose>标签
- SQL--contains用法 (转)
- boost ssl
- Android从无知到有知——NO.6