|
?
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
