直接从SQL语句问题贴子数据建表并生成建表语句的存储过程

原创 2004年01月07日 15:10:00

下面的存储过程,可帮你在回答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<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=max(ID) 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


测试
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的写法在测试中。

excel生成建表SQL

趁这个暑假有些时间,想写些“代码生成”的文章。现在代码生成工具有很多,这里就不作探讨,自己写几个小工具,主要是了解这一个过程,记录些技术点。 计划: 1:excel生成建表SQL 2:JAVA读取Ex...
  • hzaccp3
  • hzaccp3
  • 2013年07月31日 23:19
  • 12455

Excel自动生成sql建表语句

ETL开发中整理很多表结构,
  • enjoyjin
  • enjoyjin
  • 2014年07月09日 17:40
  • 4248

通过实体类生成 sql 建表语句

import java.io.BufferedWriter; import java.io.File; import java.io.FileOutputStream; import java....
  • xzl1991818
  • xzl1991818
  • 2016年09月05日 17:23
  • 1090

PowerDesigner生成sql建表语句

1.建物理模型 2.建表 如图 3.建表语句即生成 4.复制粘贴到pl/sql即可创建表 5.物理模型 pdm文件可以保存下来,下次直接双击打开即可。...
  • FightingITPanda
  • FightingITPanda
  • 2017年06月06日 14:11
  • 479

excel数据库设计表转换成sql建表语句

序号 字段英文 字段中文 主键 类型 长度 单位 空否 默认值 备注 1.     ID 自增ID   N 11     ...
  • sinat_20522337
  • sinat_20522337
  • 2017年07月18日 12:43
  • 826

利用注解,自动生成建表语句&转换查询结果

利用注解,自动生成建表语句&转换查询结果转载请注明出处: http://blog.csdn.net/zhuwentao16/article/details/49684447 需求 前段时间,为...
  • zhuwentao16
  • zhuwentao16
  • 2015年11月06日 16:13
  • 1219

用java导出数据库所有表信息,以及自己写的拼凑建表语句程序

由于公司数据库没有进行分库分表,导出上亿数据量时,内存溢出已经解决但是存在速度很慢的问题,在不允许动表情况下还未找到解决办法。 import java.io.BufferedReader; ...
  • qq_36752632
  • qq_36752632
  • 2017年05月02日 09:34
  • 1359

QL之-建库、建表、建约束、关系SQL基本语句

SQL之-建库、建表、建约束、关系SQL基本语句大全.txt举得起放得下叫举重,举得起放不下叫负重。头要有勇气,抬头要有底气。学习要加,骄傲要减,机会要乘,懒惰要除。人生三难题:思,相思,单相思。 S...
  • xiyiyindie
  • xiyiyindie
  • 2016年09月19日 10:57
  • 1760

SQL语句创建表并插入指定N条数据(用于测试)

CREATE TABLE [dbo].[TestTable]( [D_Id] [int] IDENTITY NOT NULL, [D_Name] [varchar](50) NULL, [D_P...
  • Andrew_wx
  • Andrew_wx
  • 2011年11月11日 23:43
  • 4776

SQL Server 怎样使用SQL输出建表语句

在一般的数据库系统中可使用 表名右键--[Script Table as]--[Create To]--XX 输出建表SQL语句,但在某些SQL Server系统中可能客户仅提供数据库查询权限,导致无...
  • ChristopherChen
  • ChristopherChen
  • 2016年07月13日 18:53
  • 3645
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:直接从SQL语句问题贴子数据建表并生成建表语句的存储过程
举报原因:
原因补充:

(最多只允许输入30个字)