根据基本表结构及其数据生成 INSERT ... 的 SQL
来源:互联网 发布:android解析json数据 编辑:程序博客网 时间:2024/06/09 19:19
create proc spGenINSERTSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
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">=@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),''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
print @SQL
exec (@SQL)
第二版:2003.03.08
alter 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
第三版: 2003.3.9
ALTER 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,175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
when xtype in (231,239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename) and autoval is null
) T
set @SQL ='select ''INSERT INTO ['+ @tablename + ']' + left(@SQL,len(@SQL)-1)+') ' + left(@SQLValues,len(@SQLValues)-4) + ')'' from '+@tablename
print @SQL
exec (@SQL)
/*
select *
from syscolumns
where id = object_id('test') and autoval is null
*/
end
- 根据基本表结构及其数据生成INSERT...的SQL
- 根据基本表结构及其数据生成 INSERT ... 的 SQL
- 根据基本表结构及其数据生成INSERT 语句的SQL脚本
- 根据基本表结构及其数据生成 INSERT INTO ... 的 SQL
- 根据数据库生成 sql insert 数据
- Sqlserver根据表名生成insert 插入语句的sql
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据数据字典表定义的表结构,生成创建表的SQL语句
- sql server 小数据插入,根据表名生成insert 语句,求加固
- 自动生成Insert数据的SQL脚本
- 导出数据、生成SQL INSERT的方法。
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- 根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句
- Oracle817 版本 不同字符集之间的数据库导入
- 哲学家就餐问题的C#实现
- Oracle中Blob字段的写入处理(一)
- VB.NET中的类
- ADO+ 引导数据种类的演变 (转自 ms 一)
- 根据基本表结构及其数据生成 INSERT ... 的 SQL
- 把.NET程序部署到没有安装.NET Framwork的机器上
- (interbase之六) interbase的dialect
- 图片上传到数据库并显示(C#+Access)
- [UDF系列]如何编写InterBase UDF 之三
- Questioning C# (三)
- “管家婆”软件用于维修管理
- 如何在pb9中调用WebService
- Microsoft .NET 中的基类继承(1)