在sql server中产生insert语句脚本

好久没来更新blog了，主要是最近比较忙，忙的结果是回到家什么都不想做，什么都没念头，只想倒头就睡，当然这不过是骗人的借口而已，呵呵…看来要坚持做某件事情还真的很难，就象前段时间想每天坚持学点英语，结果没几天就不了了之了：）

上次美国佬要做数据同步，数据量很小，当时想用DTS做，但美国跟苏州这里不能直接相连，做数据库备份又太大，而其所需的数据又不多，考虑再三，如果能自动生成数据插入语句，然后将生成的文件发给美国，那边执行就可以了，想法很好，但怎么实施呢？在 oracle 里面可以通过logminer来实现，在Sybase里有unload可以实现，在sql server里好象没有专门的工具或命令可实现，怎么办呢，只能自己写脚本实现了…脚本如下：

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[generate_insert_statement]‘) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[generate_insert_statement]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE generate_insert_statement
@tablename nvarchar(128),
@dbname nvarchar(128),
@objowner nvarchar(128),
@collist varchar(8000)
AS
/*
–@tablename is table name which you want to export data
–@dbname is the database name
–@objowner is the table owner ,default is dbo
@collist is column list that you want to export

*/

set nocount on

declare @errmsg varchar(100)

if isnull(@tablename,”)=”
begin
set @errmsg=’The tablename must be specified’
goto ErrH
end

set @dbname=isnull(@dbname,”)
set @objowner=isnull(@objowner,”)

declare @colname nvarchar(128)
declare @coltype nvarchar(128)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @fet_sql varchar(8000)
declare @flag smallint

if isnull(@collist,”)<>”
begin
set @collist=””+replace(@collist,’,',char(39)+char(44)+char(39))+””
end

set @fet_sql=’select sc.name ,st.name from ‘+@dbname+’.'+@objowner+’.syscolumns sc,’+@dbname+’.'+@objowner+’.systypes st
where sc.id=(select id from ‘+@dbname+’.'+@objowner+’.sysobjects where xtype=”U” and name=”’+@tablename+”’)
and sc.xtype=st.xtype and sc.xusertype=st.xusertype ‘+case when isnull(@collist,”)=” then ” else ‘and sc.name in(‘+@collist+’)’ end

exec(@fet_sql)

if @@rowcount=0
begin
set @errmsg=’The table or column does exist’
goto ErrH
end

set @fet_sql=@fet_sql+’ and (st.name in(”image”,”text”,”ntext”) or st.status=3)’
exec(@fet_sql)
if @@rowcount>0
begin
set @errmsg=’The procedures does not support column that the data type is image,text,ntext,also does not support user defined data type’
goto ErrH
end

set @flag=1
if isnull(@collist,”)=”
begin
set @sql1=’ select ”insert into ‘+@tablename
end
else
begin
set @sql1=’ select ”insert into ‘+@tablename+’(’
end
set @sql2=’ values(”’
set @fet_sql=’declare fet_col cursor for ‘+left(@fet_sql,len(@fet_sql)-56)

exec(@fet_sql)
open fet_col
fetch next from fet_col into @colname,@coltype
while(@@fetch_status=0)
begin
if isnull(@collist,”)<>”
begin
set @sql1=@sql1+@colname+’,’
end
set @flag=case when @coltype in(’int’,'bigint’,'binary’,'bit’,'decimal’,'numeric’,’smallint’,'tinyint’) then 0 when @coltype in(’datetime’,’smalldatetime’,'timestamp’) then 1 when @coltype in(’float’,'real’,'money’,’smallmoney’) then 2 else 3 end
set @sql2=@sql2+’+case when ‘+@colname+’ is null then ‘+char(39)+’null’+char(39) +’ else ‘+case when @flag in(1,3) then ‘char(39)+’+case when @flag=1 then ‘convert(varchar(20),’+@colname+’,120)’ else @colname end+’+char(39)’ when @flag=0 then ‘cast(‘+@colname+’ as varchar(50))’
when @flag=2 then ‘convert(varchar(50),’+@colname+’,2)’ end+’ end+char(44)’
fetch next from fet_col into @colname,@coltype
end
set @sql2=left(@sql2,len(@sql2)-9)
if isnull(@collist,”)<>”
begin
set @sql1=left(@sql1,len(@sql1)-1)
set @sql1=@sql1+’)’
end
set @sql2=@sql2+’+”)” from ‘+@dbname+’.'+@objowner+’.'+@tablename
close fet_col
deallocate fet_col
–print @sql1+@sql2
exec(@sql1+@sql2)
return

ErrH:
raiserror 16000 @errmsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

该脚本基本能满足需求，但不支持有用户自定义数据类型及ntext,image等数据类型的表，如果大家有什么好的解决方法，不妨留言告诉我，谢谢先：）