将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 @  

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),0))'">'+@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),''''))'
          set @bool=1
        end  
        else                                                       --如果是整数类型或者是浮点类型
        begin
       set @tempValue='values (''+convert(varchar(20),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),''''))'
            else
              set @tempValue=@tempValue+'+'',''''))'
            set @bool=1
    end  
    else                                                       --如果是整数类型或者是浮点类型
       begin
            if @bool=1
            set @tempValue=@tempValue+'+'''''',''+convert(varchar(20),0))'
            else
              set @tempValue=@tempValue+'+'',0))'
            set @bool=0
       end
      end
    end
    FETCH NEXT FROM tableInfo_Cursor  into  @tabname,@typename
END;
--print @tempStr +')' +@tempValue+')' CLOSE tableInfo_Cursor DEALLOCATE tableInfo_Cursor

总结

以上是内存溢出为你收集整理的将SQLSERVER 表记录生成插入语句全部内容，希望文章能够帮你解决将SQLSERVER 表记录生成插入语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。