将SQLSERVER 表记录生成插入语句

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[test]
as
declare @tabName varchar(50)
declare @colName varchar(50)
declare @typeName varchar(50)
declare @temp varchar(2000)
declare @tempCombineCol varchar(2000)
declare @tempStr varchar(5000)  --声成inser into tablname ()部分内容
declare @tempValue varchar(5000) --生成insert语句中 values()部门内容
declare @i int
declare @bool int   --判断前一个字段是不是字符串
set @i=0
set @bool=0
DECLARE TableInfo_Cursor CURSOR FOR
select obj.name as tblName,col.name as colName,types.name as typeName from sysobjects as obj,syscolumns as col,sys.types as types
where obj.xtype='u' and obj.id=col.id and col.xtype=types.user_type_id  and obj.name<>'dtproperties'

OPEN TableInfo_Cursor;
FETCH NEXT FROM TableInfo_Cursor into  @tabName,@colName,@typeName
WHILE @@FETCH_STATUS = 0
BEGIN
    if @i=0    --程序初始化时
    begin
      set @temp=@tabName
      --@tempCombineCol
      set @tempStr='insert into '+@tabName +' ('+@colName
      if LOWER(@typename) ='char' or LOWER(@typename)='varchar' or LOWER(@typename)='ntext'   --根据字符串类型生成insert 语句后面的值 ntext
      begin
        set @tempValue='values (''''''+convert(varchar(20),isnull('+@colName+',''''))'
        set @bool=1
      end  
      else                                                       --如果是整数类型或者是浮点类型
      begin
     set @tempValue='values (''+convert(varchar(20),isnull('+@colName+',0))'
       set @bool=0
      end
      set @i=1
    end
    else                                                        ---如果不是第一条记录
    begin
      if @temp<>@tabName                                        --如果是第二章表 
      begin
        if @bool=1
          print 'select '''+ @tempStr +')' +@tempValue+'+'''''')'' from '+ @temp
        else
          print 'select '''+ @tempStr +')' +@tempValue+'+'')'' from '+ @temp   
        set @temp=@tabName

        set @tempStr='insert into '+@tabName +' ('+@colName
        if LOWER(@typename) ='char' or LOWER(@typename)='varchar' or LOWER(@typename)='ntext'  --根据字符串类型生成insert 语句后面的值
        begin
          set @tempValue='values (''''''+convert(varchar(20),isnull('+@colName+',''''))'
          set @bool=1
        end  
        else                                                       --如果是整数类型或者是浮点类型
        begin
       set @tempValue='values (''+convert(varchar(20),isnull('+@colName+',0))'
          set @bool=0
        end
        --set @i=0
      end
      else                                                      --如果是第一个表非第一个字段
      begin
          set @tempStr=@tempStr+','+@colName
    if LOWER(@typename) ='char' or LOWER(@typename)='varchar' or LOWER(@typename)='ntext'  --根据字符串类型生成insert 语句后面的值
    begin
            if @bool=1
      set @tempValue=@tempValue+'+'''''',''''''+convert(varchar(20),isnull('+@colName+',''''))'
            else
              set @tempValue=@tempValue+'+'',''''''+convert(varchar(20),isnull('+@colName+',''''))'
            set @bool=1
    end  
    else                                                       --如果是整数类型或者是浮点类型
       begin
            if @bool=1
            set @tempValue=@tempValue+'+'''''',''+convert(varchar(20),isnull('+@colName+',0))'
            else
              set @tempValue=@tempValue+'+'',''+convert(varchar(20),isnull('+@colName+',0))'
            set @bool=0
       end
      end
    end
    FETCH NEXT FROM TableInfo_Cursor  into  @tabName,@colName,@typeName
END;
--print @tempStr +')' +@tempValue+')'
CLOSE TableInfo_Cursor
DEALLOCATE TableInfo_Cursor