; set @m=@m-1
end
end
if @i<len(@a) set @a=ltrim(right(@a,len(@a)-@i))
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.''''+@table_name) is not null
exec(''''drop table ''''+@table_name)
-- 建表
update a
set a.fld_name=b.val
from @dt a,#xx b
where a.ID=b.ID and a.ID<=@num1
set @a=''''''''
select @a=@a+fld_name+'''' ''''+fld_type+'''','''' from @dt where ID<=@num1
set @a=left(@a,len(@a)-1)
set @sql=''''create table ''''+@table_name+''''(''''+@a+'''')''''
exec(@sql)
insert into @sqlt select @sql
--插入数据
set @i=@num1+1
while @i<=(select max(ID) from #xx)
begin
set @a=''''''''
set @sql=''''select @s=@s+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 ''''+@table_name+'''' select ''''+@a
if len(@a)>0 exec(@sql)
insert into @sqlt select @sql
set @i=@i+@num1
end
select * from @sqlt
--select * from @dt
exec(''''select * from ''''+@table_name)
SET NOCOUNT OFF
END
测试
exec dbo.create_table ''''##t2'''',''''varchar(20),datetime k'''',''''
ID AnDate
99101 2002-11-24 00:00:00.000
99101 2003-11-15 00:00:00.000
99101 2003-11-29 00:00:00.000
99101 2003-12-20 00:00:00.000''''
结果
sql_statement
--------------------------------------------------------
create table ##t2(ID varchar(20),AnDate datetime)
insert into ##t2 select ''''99101'''',''''2002-11-24 00:00:00.000''''
insert into ##t2 select ''''99101'''',''''2003-11-15 00:00:00.000''''
insert into ##t2 select ''''99101'''',''''2003-11-29 00:00:00.000''''
insert into ##t2 select ''''99101'''',''''2003-12-20 00:00:00.000''''
ID AnDate
-------------------- ---------------------------
99101 2002-11-24 00:00:00.000
99101 2003-11-15 00:00:00.000
99101 2003-11-29 00:00:00.000
99101 2003-12-20 00:00:00.000
ORACLE的写法在测试中。
上一页 [1] [2] | 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 