批量生成数据库某个表的insert语句

需求描述:现在在A机器上有一张表T,并且有数据,想生成批量的insert语句后,导入到A机器上的表T中。


解决办法:有两个方案


(1)方案1:通过存储过程做:如下:

create proc proc_insert (@tablename varchar(256))  
as  
begin  
set nocount on  
declare @sqlstr varchar(4000)  
declare @sqlstr1 varchar(4000)  
declare @sqlstr2 varchar(4000)
select 'SET IDENTITY_INSERT '+@tablename +' ON'  
select @sqlstr='select ''insert '+@tablename  
select @sqlstr1=''  
select @sqlstr2=' ('  
select @sqlstr1= ' values ( ''+'  
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case  
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'  
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'  
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'  
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'  
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'  
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'  
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'  
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'  
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'  
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'  
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'  
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'  
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'  
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'  
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'  
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'  
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'  
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'  
else '''NULL'''  
end as col,a.colid,a.name  
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36  
)t order by colid  

select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename  
-- print @sqlstr  
exec( @sqlstr)  
select 'SET IDENTITY_INSERT '+@tablename +' OFF'
set nocount off  
end  
go  


执行:proc_insert  T


知识延伸:syscolumns xtype

xtype    类型
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar


(2)方案2:

通过循环搞,不智能。

declare @str varchar(8000)
declare @i int 
set @i=1
while(@i<124)
begin 
SELECT @str='INSERT INTO Menu(MenuName,MenuIds,ParentId,LinkUrl,Value) VALUES
('+MenuName+',''' + MenuIds+ ''',''' + cast(ParentId as varchar(50))+ ''',''' +LinkUrl+ ''',''' + Value+ ''')' FROM Menu where MenuId=@i
set @i=@i+1
print @str
end


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/cafardhaibin/article/details/7388466
个人分类: 学习体会
上一篇vc6.0程序到vs 2005的编译问题
下一篇Timesten
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭