新年送礼:给你一个直接从贴子数据建表的小工具

OpenVMS 2004-01-02 05:29:47
/*Create Table from your web page data
* 2004-JAN-1, OpenVMS,V0.1
* Sample Call: in SQL Query Analyzer
exec dbo.create_table '##1','varchar(10),int,int,int,int',
'No a b c d
001 2 4 NULL NULL
002 3 NULL 2 3
003 8 7 1 null'

注意:
1 如用临时表名,只能用全局临时表 ##,否则不可访问
2 如果没有列名,则需要在第一行数据手动加上列名
3 字段名称不允许含空格
4 至少一行数据,否则没有意义
5 字段值为空需要写上NULL,字段值中的任何符号作为值的一部分
6 没有对定义类型和值的类型匹配检查
*/


/*Create Table from your web page data
* 2004-JAN-1, OpenVMS
* Sample Call: in SQL Query Analyzer
exec dbo.create_table '#1','datetime,varchar(10),int','
时间 名称 数据
2000325 name1 1
2001325 name2 2
2002325 name3 3'

注意:如果没有列名,则需要在第一行数据手动加上列名
*/

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 paste from web page
AS
BEGIN
declare @dt table(id int identity(1,1),fld_name varchar(30),fld_type varchar(20))
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(1)

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=replace(@datatype,' ','')
set @tmp=@datatype
set @i=1
set @num1=0

while @i>0
begin
select @i=charindex(',',@datatype)
if @i>1
begin
insert into @dt(fld_type) values(left(@datatype,@i-1))
select @datatype=right(@datatype,len(@datatype)-@i)
end
if @i=0 and len(@datatype)>0
insert into @dt(fld_type) values (@datatype)
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 @num1=@num1+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=@str
set @num2=0
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 @m=0
while len(@a)>0
begin
set @i=1
set @x=left(@a,1)
if @x not in (' ',char(13),char(10))
begin
set @i=charindex(' ',@a)
set @j=charindex(char(13)+char(10),@a)
set @k=@k+1
set @m=@m+1
if @j<@i and @j>0 begin
set @i=@j
if @k>@num2 set @num2=@k
set @k=0
end
if @i=0 set @i=(case when @j>0 then @j else len(@a)+1 end)
insert into #xx(val) values(left(@a,@i-1))
end
if @i<len(@a) set @a=ltrim(right(@a,len(@a)-@i))
else set @a=''
if @x=char(13) set @k=0
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)

--插入数据
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)

set @i=@i+@num1
end

select * from @dt
exec('select * from '+@table_name)
SET NOCOUNT OFF
END
...全文
91 47 打赏 收藏 转发到动态 举报
写回复
用AI写文章
47 条回复
切换为时间正序
请发表友善的回复…
发表回复
prcgolf 2004-01-07
  • 打赏
  • 举报
回复
多谢楼主!!
OpenVMS 2004-01-06
  • 打赏
  • 举报
回复
/*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<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,@i-1+@m),case when @m=-1 then 1 else 0 end)
select @datatype=right(@datatype,len(@datatype)-@i)
end
if @i=0 and len(@datatype)>0
insert into @dt(fld_type,blank) values(left(@datatype,len(@datatype)+@m),
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 @num1=@num1+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 @m=@m+1
if @k<>-1 set @k=@k+1
if @j>0 and (@j<@i or @j>@i and substring(@a,@i,@j-@i)=space(@j-@i)) begin
set @i=@j
if @k>@num2 and @k<>-1 set @num2=@k
set @k=-1
end
if @i=0 set @i=(case when @j>0 then @j else len(@a)+1 end)

select @j=isnull(max(ID),0) from #xx
if @m=1 or @j<=@num1 or (select blank from @dt where ID=@m-1) <> 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 ID=@j
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

OpenVMS 2004-01-06
  • 打赏
  • 举报
回复
exec dbo.create_table '##t2','varchar(20),datetime k','
旅[费]大 随&风倒]机''洒的旅费
99''101 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([旅[费]]大] varchar(20),[随&风倒]]机''洒的旅费] datetime)
insert into ##t2 select '99''101','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'

旅[费]大 随&风倒]机''洒的旅费
-------------------- ------------------------------------------------------
99'101 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
OpenVMS 2004-01-06
  • 打赏
  • 举报
回复
/*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<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,@i-1+@m),case when @m=-1 then 1 else 0 end)
select @datatype=right(@datatype,len(@datatype)-@i)
end
if @i=0 and len(@datatype)>0
insert into @dt(fld_type,blank) values(left(@datatype,len(@datatype)+@m),
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 @num1=@num1+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=replace(@str,'''','''''') --- ' 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 @m=@m+1
if @k<>-1 set @k=@k+1
if @j>0 and (@j<@i or @j>@i and substring(@a,@i,@j-@i)=space(@j-@i)) begin
set @i=@j
if @k>@num2 and @k<>-1 set @num2=@k
set @k=-1
end
if @i=0 set @i=(case when @j>0 then @j else len(@a)+1 end)

select @j=isnull(max(ID),0) from #xx
if @m=1 or @j<=@num1 or (select blank from @dt where ID=@m-1) <> 1
begin
if @j<@num1 set @x='['+replace(rtrim(left(@a,@i-1)),']',']]')+']'
else set @x=rtrim(left(@a,@i-1))
insert into #xx(val) values(@x)
end
else
begin
update #xx set val=val+' '+rtrim(left(@a,@i-1)) where ID=@j
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

prcgolf 2004-01-06
  • 打赏
  • 举报
回复
to:
OpenVMS(半知半解)
多谢!!
还有点小BUG.
exec dbo.create_table '##t2','varchar(20),datetime k','
旅[费]大 随&风倒]机''洒的旅费
99''101 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'

我是想生成的表的第二个字段名为:

随&风倒]机'洒的旅费 --一个单引号

而不是:

随&风倒]机''洒的旅费 --这里是两个单引号



lvltt 2004-01-05
  • 打赏
  • 举报
回复
收藏 学习!
erigido 2004-01-05
  • 打赏
  • 举报
回复
收藏先
SunWinter 2004-01-05
  • 打赏
  • 举报
回复
另外问zjcxc(邹建) 一个问题:

邹建,昨天看到你的帖子<<交流--数据库备份/恢复方案>>,觉得很不错,现提出一个小小的建议,在p_RestoreDB中,在恢复的文件名中加了文件顺序号,例如:Test0.mdf,Test1.ldf,
我建议做如下改动,
如果mdf和ldf文件只有一个,则不要加入文件顺序号,如Test.mdf,Test_log.ldf
如果有多个mdf和ldf文件,则可以加入文件顺序号,如Test0.mdf,Test1.mdf,Test_log2.ldf
Test_log3.ldf
你觉得如何呢?
如愿采纳,请将新的文件公布出来,谢谢。
SunWinter 2004-01-05
  • 打赏
  • 举报
回复
收藏。

sunrisehy2003 2004-01-05
  • 打赏
  • 举报
回复
收到,謝謝
yulinlin 2004-01-05
  • 打赏
  • 举报
回复
收藏
sunnyyin 2004-01-05
  • 打赏
  • 举报
回复
我也收藏
litf 2004-01-05
  • 打赏
  • 举报
回复
学习ing
愉快的登山者 2004-01-05
  • 打赏
  • 举报
回复
好东东。
prcgolf 2004-01-05
  • 打赏
  • 举报
回复
请修改特殊字符的BUG:
exec dbo.create_table '##t2','varchar(20),datetime k','
旅[费]大 随&风倒]机''洒的旅费
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'

服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '大' 附近有语法错误。
服务器: 消息 105,级别 15,状态 1,行 1
字符串 '的旅费 datetime)' 之前有未闭合的引号。

shuijun 2004-01-05
  • 打赏
  • 举报
回复
收藏!:)
prcgolf 2004-01-05
  • 打赏
  • 举报
回复
to:
OpenVMS(半知半解)
下面的是正确的:
create table ##t2([旅[费]]大] varchar(20),[随&风倒]]机'洒的旅费] 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'

select * from ##t2

drop table ##t2
OpenVMS 2004-01-05
  • 打赏
  • 举报
回复
旅[费]大 随&风倒]机''洒的旅费
作为字段名,本来就非法,不好改 :-)
1ssp 2004-01-05
  • 打赏
  • 举报
回复
收藏
leus 2004-01-05
  • 打赏
  • 举报
回复
强啊!
加载更多回复(27)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧