导出sqlserver2005数据表为sql语句

[cnhnhsh]

请教高手们：如何把sqlserver2005数据库中的某个表导出为sql语语言的insert语句（当然要包括数据），谢谢。

[cnhnhsh]

谢谢，但在sqlserver2005查询中执行EXEC pGetInsertSQL('dept')时提示有语法错误，其中dept是数据表。

[cnhnhsh]

本人的意思是求出从sqlserver2005数据库里导出某个数据表的insert语句代码，而不是数据表的结果集，楼上兄弟的代码还不能满足要求。

[Novelty]

这是从microsoft得到的代码http://download.csdn.net/source/445640

[pgy8288]

http://download.csdn.net/source/336474
一个专门针对SQL Server的数据导出小工具.

[lishuaide0517]

用个游标写吧
declare @id int
declare @year nvarchar(4)
declare @decrip nvarchar(50)
declare c_cursor cursor
for
select * from tab1
open c_cursor
fetch next from c_cursor into @id,@year,@decrip
while @@fetch_status=0
begin

print 'id:'+cast(@id as nvarchar(10))+' year:'+@year+'\n'
fetch next from c_cursor into @id,@year,@decrip
end
close c_cursor
deallocate c_cursor

[dawugui]

--参考:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE proc pGetInsertSQL (@TableName varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
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)
set nocount off
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[wangxuelid]

jl

[pgy8288]

http://download.csdn.net/source/336474
就是专门导出insert语句的。

[utpcb]

你可以用软代码生成器

[忆轩辕]

一个简单的方法。。。。。。。select * from 表名

把结果复制到excel表格里面，自己手工完成语句

不过这个方法不适合大数据量的表

[ljm311]

[Quote=引用 2 楼 cnhnhsh 的回复:]
谢谢，但在sqlserver2005查询中执行EXEC pGetInsertSQL('dept')时提示有语法错误，其中dept是数据表。
[/Quote]
执行方式：exec pGetInsertSQL dept
然后将生成的结果导出为csv即可。