Excel自动生成sql建表语句

最新推荐文章于 2024-02-26 17:15:23 发布

enjoyjin

最新推荐文章于 2024-02-26 17:15:23 发布

阅读量1.5w

点赞数 2

分类专栏：其他文章标签： excel sql 数据库自动生成

本文链接：https://blog.csdn.net/enjoyjin/article/details/37602427

版权

其他专栏收录该内容

2 篇文章 0 订阅

订阅专栏

ETL开发中重新整理很多表结构，写建表语句又是一件头疼的事情，干脆借鉴网友的代码，自己写了一个自动生成建表sql的模板。
模板下载：http://download.csdn.net/detail/enjoyjin/7612827

参考代码如下：

Sub 生成目录()
    '生成表目录
       For si = 2 To Workbooks(1).Sheets.Count '从第二张表开始,遍历每一张表
        Set mysheet = Workbooks(1).Sheets(si) '表
        Workbooks(1).Sheets(1).Range("D" & si).Value = mysheet.Name '中文名
        Workbooks(1).Sheets(1).Range("D" & si).Hyperlinks.Add Anchor:=Workbooks(1).Sheets(1).Range("D" & si), Address:="", SubAddress:="'" & mysheet.Name & "'!R1C1"
        Workbooks(1).Sheets(1).Range("C" & si).Value = mysheet.Range("A2").Value  '英文名
        Workbooks(1).Sheets(1).Range("A" & si).Value = si - 1 '序列
        
       Next si
   
End Sub
Sub 生成SQL()
    '生成建表hql
        Dim sql As String
        Dim tableName As String
        Dim comm As String
       
        For si = 2 To Workbooks(1).Sheets.Count '从第二张表开始,遍历每一张表
      
            Set mysheet = Workbooks(1).Sheets(si) '表
            tableName = mysheet.Range("A2").Value '英文表名
              
              
            '如果数据库中已存在表，则删除表
            'if exists (select * from sysobjects where name='tableName')
            'sql = sql & "if exists (select * from all_tables where name='" & tableName & "') " & vbCrLf
              
            'drop table tableName
            sql = sql & "drop table  " & tableName & " ;" & vbCrLf
            'sql = sql & "go " & vbCrLf
              
            '开始创建表
            'create table tableName (
            sql = sql & "create table " & tableName & "  ( " & vbCrLf
            comm = ""
              
            For i = 2 To mysheet.UsedRange.Rows.Count '遍历所有的列
                Dim nameStr As String
                Dim typeStr As String
              
                nameStr = mysheet.Range("B" & i).Value '字段名
                typeStr = mysheet.Range("D" & i).Value '数据类型
                refStr = mysheet.Range("E" & i).Value '是否主键
                commStr = mysheet.Range("C" & i).Value '中文注释
                  
                If refStr = "是" Then
                    sql = sql & "   " & nameStr & " " & typeStr & " primary key" '默认id为主键
                Else
                    sql = sql & "   " & nameStr & " " & typeStr '非id列,则直接加入
                End If
                 
                If Len(commStr) > 0 Then
                    comm = comm & vbCrLf & "comment on column " & tableName & "." & nameStr & "  is " & "'" & commStr & "';"
                End If
                
                If i < mysheet.UsedRange.Rows.Count Then
                    sql = sql & "," '添加‘,’号
                End If
                  
                 sql = sql & vbCrLf
            Next i
              
            '建表完成
            sql = sql & ")" & vbCrLf
            sql = sql & "tablespace  JFXTDATA   pctfree 10   initrans 1   maxtrans 255   storage   (     initial 64K     next 1M     minextents 1   maxextents unlimited   ) ;" & vbCrLf
            sql = sql & comm & vbCrLf & "-----Create table " & tableName & " end." & vbCrLf & vbCrLf
          
        Next si
    
       Sheets("目录").txtConsole.Text = sql
       
End Sub