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

结果
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]