下面的存储过程，可帮你在回答SQL语句问题时，直接从贴子的样本数据建表并生成建表语句，省去大量的手工输入数据的工作。
/*Create Table from your web page data
- 2004-JAN-1, OpenVMS，V0.1
- 2004-JAN-2, V0.5, add tab & blank values logical
- 2004-JAN-3, V1.0, add SQL Statement generation
- 2004-JAN-4, V1.1, fix datatype like decimal(4,2) bug
- 2004-JAN-4, V1.2, fix field name bug
- Sample Call: in SQL Query Analyzer
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'
注意：
1 如用临时表名，只能用全局临时表 ##，否则不可访问
2 如果没有列名，则需要在第一行数据手动加上列名
3 字段名称不允许含空格
4 至少一行数据，否则没有意义
5 字段值为空需要写上NULL，字段值中的任何符号作为值的一部分
6 没有对定义类型和值的类型匹配检查
7 可指定值中含有空格，方法为在该类型定义中的尾部加字母 k, 如 datatime k,
8 如过值中含有单引号，需要复写 ' －》''
*/
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 '+@table_name+' exists,choose a new one!'
RAISERROR (@a,16,1)
return
end
--提取类型名
set @datatype=lower(replace(@datatype,' ',''))
set @tmp=@datatype
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
1<charindex(')',@datatype) (@j="" @i="charindex(')',@datatype)+1" @j="charindex('k',@datatype)" @m="0" if="" select="" set="">1 and @j<@i) or (@i=0 and @j=len(@datatype)) set @m=-1
2if @i>1
3begin
4insert into @dt(fld_type,blank)
5values(left(@datatype,@i-1+@m),case when @m=-1 then 1 else 0 end)
6select @datatype=right(@datatype,len(@datatype)-@i)
7end
8if @i=0 and len(@datatype)>0
9insert into @dt(fld_type,blank) values(left(@datatype,len(@datatype)+@m),
10case when @m=-1 then 1 else 0 end)
11if @i=1 or len(@datatype)=0
12begin
13RAISERROR ('error data type,comma sign can not be a prefix or surfix',16,1)
14return
15end
16
17set @num1=@num1+1
18end
19
20\--检查类型
21if exists (select fld_type from @dt
22where (case when charindex('(',fld_type)>0 then
23left(fld_type,charindex('(',fld_type)-1)
24else fld_type end) not in (select name from systypes) or
25charindex('(',fld_type)*charindex(')',fld_type)=0 and
26charindex('(',fld_type)+charindex(')',fld_type)>0)
27begin
28RAISERROR ('error data type.', 16, 1)
29return
30end
31
32\--提取字段和数据
33set @a=replace(@str,char(9),' ') --- TAB char
34set @a=rtrim(ltrim(@a))
35if charindex(char(13)+char(10),right(@a,len(@a)-1))=0 or len(@a)=0
36begin
37RAISERROR ('input data error,check your data.', 16, 1)
38return
39end
40
41if object_id('tempdb.dbo.#xx') is not null drop table #xx
42select identity(int,1,1) ID,space(50) val into #xx where 1=2
43set @k=0
44set @num2=0
45set @m=0
46while len(@a)>0
47begin
48set @i=1
49set @x=left(@a,1)
50
51if @x=char(10) begin
52if @m>@num2 and @num2>0 and charindex('k',@datatype)=0 begin
53RAISERROR ('number of data is greater than the columns,you should add k in data type difinition.', 16, 1)
54return
55end
56set @m=0
57end
58
59if @x not in (' ',char(13),char(10))
60begin
61set @i=charindex(' ',@a)
62set @j=charindex(char(13)+char(10),@a)
63set @m=@m+1
64if @k<>-1 set @k=@k+1
65if @j>0 and (@j<@i or @j>@i and substring(@a,@i,@j-@i)=space(@j-@i)) begin
66set @i=@j
67if @k>@num2 and @k<>-1 set @num2=@k
68set @k=-1
69end
70if @i=0 set @i=(case when @j>0 then @j else len(@a)+1 end)
71
72select @j=max(ID) from #xx
73if @m=1 or @j< =@num1 or (select blank from @dt where ID=@m-1 ) <> 1
74begin
75if @j<@num1 set @x='['+replace(rtrim(left(@a,@i-1)),']',']]')+']'
76else set @x=rtrim(left(replace(@a,'''',''''''),@i-1))
77insert into #xx(val) values(@x)
78end
79else
80begin
81update #xx set val=val+' '+rtrim(left(@a,@i-1)) where ID=@j
82set @m=@m-1
83end
84end
85if @i<len(@a) #xx="" ''+val+''''="" @a="" else="" end="" id="" set="" update="" val="" where="">@num2
86
87if @num1<>@num2
88begin
89RAISERROR ('datatype dismatch the columns',16,1)
90return
91end
92
93\-- if use the exists template table,drop it
94if object_id( 'tempdb.dbo.'+@table_name ) is not null
95exec('drop table '+@table_name )
96
97\-- 建表
98update a
99set a.fld_name=b.val
100from @dt a,#xx b
101where a.ID=b.ID and a.ID< =@num1
102
103set @a=''
104select @a=@a+fld_name+' '+fld_type+',' from @dt where ID< =@num1
105set @a=left(@a,len(@a)-1)
106set @sql='create table '+@table_name+'('+@a+')'
107exec(@sql)
108insert into @sqlt select @sql
109
110\--插入数据
111set @i=@num1+1
112while @i<=(select max(ID) from #xx)
113begin
114set @a=''
115set @sql='select @s=@s+val+'','''+' from (select top '+convert(varchar(10),@num1)
116+' val from #xx where ID>='+convert(varchar(10),(@i))+') a'
117exec sp_executesql @sql,N'@s nvarchar(3000) output',@a output
118
119set @a=left(@a,len(@a)-1)
120
121set @sql='insert into '+@table_name+' select '+@a
122if len(@a)>0 exec(@sql)
123insert into @sqlt select @sql
124
125set @i=@i+@num1
126end
127
128select * from @sqlt
129\--select * from @dt
130exec('select * from '+@table_name )
131SET NOCOUNT OFF
132END
133
134* * *
135
136测试
137exec dbo.create_table '##t2','varchar(20),datetime k','
138ID AnDate
13999101 2002-11-24 00:00:00.000
14099101 2003-11-15 00:00:00.000
14199101 2003-11-29 00:00:00.000
14299101 2003-12-20 00:00:00.000'
143
144结果
145sql_statement
146\--------------------------------------------------------
147create table ##t2(ID varchar(20),AnDate datetime)
148insert into ##t2 select '99101','2002-11-24 00:00:00.000'
149insert into ##t2 select '99101','2003-11-15 00:00:00.000'
150insert into ##t2 select '99101','2003-11-29 00:00:00.000'
151insert into ##t2 select '99101','2003-12-20 00:00:00.000'
152
153ID AnDate
154\-------------------- ---------------------------
15599101 2002-11-24 00:00:00.000
15699101 2003-11-15 00:00:00.000
15799101 2003-11-29 00:00:00.000
15899101 2003-12-20 00:00:00.000
159
160
161ORACLE的写法在测试中。</len(@a)></charindex(')',@datatype)>