; 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] [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|