if exists (select name from sysobjects where name = n'create_table' and type = 'p') drop procedure create_table go
create proc dbo.create_table @table_name varchar(60),--- table name @datatype varchar(1000),--- separated by comma ',' @str nvarchar(3000) --- input string pasted from web page as begin declare @dt table(id int identity(1,1),fld_name varchar(30),fld_type varchar(20),blank int) declare @sqlt table(sql_statement varchar(8000)) declare @tmp varchar(1000),@num1 int,@num2 int,@sql nvarchar(4000) declare @a nvarchar(3000),@i int,@j int,@k int,@m int,@x nvarchar(1000)
set nocount on if object_id(@table_name) is not null begin set @a='table '[email protected]_name+' exists,choose a new one!' raiserror (@a,16,1) return end
--提取类型名 set @datatype=lower(replace(@datatype,' ','')) set @[email protected] set @i=1 set @num1=0
while @i>0 begin select @i=charindex(',',@datatype) --check datatype like decimal(10,4) if @i>charindex('(',@datatype) and @i<charindex(')',@datatype) set @i=charindex(')',@datatype)+1 select @j=charindex('k',@datatype) set @m=0 if (@j>1 and @j<@i) or (@i=0 and @j=len(@datatype)) set @m=-1 if @i>1 begin insert into @dt(fld_type,blank) values(left(@datatype,@[email protected]),case when @m=-1 then 1 else 0 end) select @datatype=right(@datatype,len(@datatype)[email protected]) end if @i=0 and len(@datatype)>0 insert into @dt(fld_type,blank) values(left(@datatype,len(@datatype)[email protected]), case when @m=-1 then 1 else 0 end) if @i=1 or len(@datatype)=0 begin raiserror ('error data type,comma sign can not be a prefix or surfix',16,1) return end
set @[email protected]+1 end
--检查类型 if exists (select fld_type from @dt where (case when charindex('(',fld_type)>0 then left(fld_type,charindex('(',fld_type)-1) else fld_type end) not in (select name from systypes) or charindex('(',fld_type)*charindex(')',fld_type)=0 and charindex('(',fld_type)+charindex(')',fld_type)>0) begin raiserror ('error data type.', 16, 1) return end
--提取字段和数据 set @a=replace(@str,char(9),' ') --- tab char set @a=rtrim(ltrim(@a)) if charindex(char(13)+char(10),right(@a,len(@a)-1))=0 or len(@a)=0 begin raiserror ('input data error,check your data.', 16, 1) return end
if object_id('tempdb.dbo.#xx') is not null drop table #xx select identity(int,1,1) id,space(50) val into #xx where 1=2 set @k=0 set @num2=0 set @m=0 while len(@a)>0 begin set @i=1 set @x=left(@a,1)
if @x=char(10) begin if @m>@num2 and @num2>0 and charindex('k',@datatype)=0 begin raiserror ('number of data is greater than the columns,you should add k in data type difinition.', 16, 1) return end set @m=0 end
if @x not in (' ',char(13),char(10)) begin set @i=charindex(' ',@a) set @j=charindex(char(13)+char(10),@a) set @[email protected]+1 if @k<>-1 set @[email protected]+1 if @j>0 and (@j<@i or @j>@i and substring(@a,@i,@[email protected])=space(@[email protected])) begin set @[email protected] if @k>@num2 and @k<>-1 set @[email protected] set @k=-1 end if @i=0 set @i=(case when @j>0 then @j else len(@a)+1 end)
select @j=max(id) from #xx if @m=1 or @j<[email protected] or (select blank from @dt where [email protected]) <> 1 begin if @j<@num1 set @x='['+replace(rtrim(left(@a,@i-1)),']',']]')+']' else set @x=rtrim(left(replace(@a,'''',''''''),@i-1)) insert into #xx(val) values(@x) end else begin update #xx set val=val+' '+rtrim(left(@a,@i-1)) where [email protected] set @[email protected] end end if @i<len(@a) set @a=ltrim(right(@a,len(@a)[email protected])) else set @a='' end
update #xx set val='' where val='null' update #xx set val=''''+val+'''' where id>@num2
if @num1<>@num2 begin raiserror ('datatype dismatch the columns',16,1) return end
-- if use the exists template table,drop it if object_id('tempdb.dbo.'[email protected]_name) is not null exec('drop table '[email protected]_name)
-- 建表 update a set a.fld_name=b.val from @dt a,#xx b where a.id=b.id and a.id<[email protected]
set @a='' select @[email protected]+fld_name+' '+fld_type+',' from @dt where id<[email protected] set @a=left(@a,len(@a)-1) set @sql='create table '[email protected]_name+'('[email protected]+')' exec(@sql) insert into @sqlt select @sql
--插入数据 set @[email protected]+1 while @i<=(select max(id) from #xx) begin set @a='' set @sql='select @[email protected]+val+'','''+' from (select top '+convert(varchar(10),@num1) +' val from #xx where id>='+convert(varchar(10),(@i))+') a' exec sp_executesql @sql,n'@s nvarchar(3000) output',@a output
set @a=left(@a,len(@a)-1)
set @sql='insert into '[email protected]_name+' select '[email protected] if len(@a)>0 exec(@sql) insert into @sqlt select @sql
set @[email protected][email protected] end
select * from @sqlt --select * from @dt exec('select * from '[email protected]_name) set nocount off end
