关闭

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

342人阅读 评论(0) 收藏 举报

需求描述:现在在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


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:171762次
    • 积分:2908
    • 等级:
    • 排名:第12119名
    • 原创:131篇
    • 转载:12篇
    • 译文:0篇
    • 评论:33条
    最新评论
    我的兄弟