vba将Excel当前活动工作表内容一键生成能够插入到oracle数据库的sql语句

likeyouli

内容为原创，ai生成的会有很多小毛病，且很多没有用数组，生成的内容直接粘贴到sql文件里，在sqlplus里执行即可。

Sub 将活动表格内容生成为oracle制表sql语句并直接粘贴()
'需要确保在VBA编辑器中已引用"Microsoft Forms 2.0 Object Library"（通常在引用列表中显示为"FM20.dll"）
    Dim strText As String, colTypes As String, values As String
  '首先生成create表语句,默认首行为标题
arr = Range("a1").CurrentRegion
TableName = InputBox("请输入Oracle表名:", "表名输入", "YOUR_TABLE_NAME")
If Not TableName Like "[a-zA-Z]*" Then MsgBox "表名不是以字母开头,请从新开始": Exit Sub
For i = 1 To UBound(arr, 2)
If  Len(Trim(arr(1, i))) = 0 Then MsgBox "第一行有空单元格": Exit Sub
Next i
For i = 1 To UBound(arr, 2)
    colnames = colnames & arr(1, i)
    If i < UBound(arr, 2) Then colnames = colnames & ","
    colTypes = colTypes & arr(1, i) & " varchar2(800)," & Chr(13)
Next i
   colTypes = Left(colTypes, InStrRev(colTypes, ",") - 1) & Mid(colTypes, InStrRev(colTypes, ",") + 1)
'以上为生产建表语句   
For j = 2 To UBound(arr)
      values = ""
    For k = 1 To UBound(arr, 2)
        if arr(j,k) = "" then    '不用 If Len(Trim(arr(j, k))) = 0 Then了,因为几十万大数据量时这样判断速度过慢, varchar2类型哪怕是null都不会报错。
            values = values & "null"
'         ElseIf k =6 Then values = values & "TO_DATE(REGEXP_SUBSTR('" & arr(j, k) & "','^[0-9-]+'),'YYYY-MM-DD')"  '个人认为这里是我写的精华,当遇到特殊列,比如带星期几的日期列,可用正则表达式提取出来,当然前边的生成列的数据类型要改为date类型.这里的数字6为要更改第几列就写几.
         Else
            values = values & "'" & Replace(arr(j, k), "'", "''") & "'"
        End If
         If k < UBound(arr, 2) Then values = values & ","
    Next k
        insertsql = insertsql & "INSERT INTO " & TableName & "(" & colnames & ")  VALUES(" & values & ");" & Chr(13) 'insert into有列名
      '  insertsql = insertsql & " INSERT INTO " & TableName & " VALUES(" & values & ");" & Chr(13)
        If j Mod 300 = 0 Then insertsql = insertsql & "commit;" & Chr(13)  '这里的300可任意改,代表间隔几列commit一次.
    If j = 200000 Then Exit For  '可以设置到二十万行结束
    DoEvents
    Application.StatusBar = Format(j / UBound(arr), "0.0%")  '大数据量可以看看进度
Next j
strText = "set echo on;" & Chr(13) & "CREATE TABLE  " & TableName & Chr(13) & "(" & colTypes & ")" & Chr(13) & _
            "tablespace users storage (next 10M) nologging;" & Chr(13) & "commit;" & Chr(13) & insertsql & "commit;"
    ' 将文本放入剪贴板
    With New DataObject
        .SetText strText
        .PutInClipboard
    End With
    MsgBox "生成的sql语句已复制到粘贴板！"
End Sub

小灰兔

感谢分享

yc2428

谢谢分享！

wn168cn@163.com

感谢分享

guong

谢谢分享

hmaaaa

THX~!感恩分享摟，謝謝大大喔~~!辛苦了！^^

wwss707

谢谢分享！

yzszh64

感谢分享，

wang1126

谢谢楼主分享

classMain

感谢分享

likeyouli

Sub 将活动表格标题生成sqlserver建表语句()
'需要确保在VBA编辑器中已引用"Microsoft Forms 2.0 Object Library"（通常在引用列表中显示为"FM20.dll"）
    Dim strText As String, colTypes As String, values As String
  '首先生成create表语句,默认首行为标题
arr = Range("a1").CurrentRegion
TableName = InputBox("请输入Oracle表名:", "表名输入", "YOUR_TABLE_NAME")
'If Not TableName Like "[a-zA-Z]*" Then MsgBox "表名不是以字母开头,请从新开始": Exit Sub
For i = 1 To UBound(arr, 2)
If Len(Trim(arr(1, i))) = 0 Then MsgBox "第一行有空单元格": Exit Sub
Next i
For i = 1 To UBound(arr, 2)
    colnames = colnames & arr(1, i)
    If i < UBound(arr, 2) Then colnames = colnames & ","
    colTypes = colTypes & arr(1, i) & " varchar(800)," & Chr(13)
Next i
   colTypes = Left(colTypes, InStrRev(colTypes, ",") - 1) & Mid(colTypes, InStrRev(colTypes, ",") + 1)
'以上为生产建表语句
strText = "CREATE TABLE  " & "[dbo].[" & TableName & _
"](" & Chr(13) & colTypes & ") ON [PRIMARY]"

    ' 将文本放入剪贴板
    With New DataObject
        .SetText strText
        .PutInClipboard
    End With
    MsgBox "生成的sql语句已复制到粘贴板！"
End Sub

sjp060305

虽然不知道LZ在说什么但是感觉很厉害的样子～

aduge38

不明觉厉啊，谢谢分享

likeyouli

"'"&A2&"',"&"'"&B2&"',"&"'"&C2&"',"&"'"&D2&"',"&"'"&E2&"',"&"'"&F2&"',"&"'"&G2&"',"&"'"&H2&"',"&"'"&I2&"',"&"'"&J2&"',"&"'"&K2&"',"&"'"&L2&"',"&"'"&M2&"',"&"'"&N2&"',"&"'"&O2&"',"&"'"&P2&"',"&"'"&Q2&"',"&"'"&R2&"',"&"'"&S2&"',"&"'"&T2&"',"&"'"&U2&"',"&"'"&V2&"',"&"'"&W2&"',"&"'"&X2&"',"&"'"&Y2&"',"&" '"&Z2&"'"       ----------------------------------------------------------------------
"INSERT INTO nihao VALUES("&AA2&");"

likeyouli

在行: 1 上开始执行命令时出错 -
INSERT INTO NIHAO VALUES
('11413053010003679771','37083019990129394X','胡秋英','居民','H37083000077','东和县人民医院','2518363-001','','BI63900','住院诊查费','22','1','22','2025-07-01','22','0','0','0','110200005','住院诊查费','','','0','0','', ''),
('11413053010003679771','37083019990129394X','胡秋英','居民','H37083000077','东和县人民医院','2518363-001','','BI63900','三人间（一等）','45','1','45','2025-07-01','35','0','10','0','110900001b1','三人间（一等）','','','0','10','', ''),
('14370830193701125000','370830199901125711','薛芳茂','职工','H37083000077','东和县人民医院','*37000000002115473456','帕金森综合征','M02301','卡左双多巴缓释片','1.655667','120','198.68','2025-07-06','0','178.81','0','19.87','XN04BAK135A010010104495','卡左双多巴缓释片','卡比多巴50mg,左旋多巴200mg','卡左双多巴缓释片','0','0','e7', '')
错误位于命令行: 3 列: 43
错误报告 -
SQL 错误: ORA-00933: SQL 命令未正确结束

https://docs.oracle.com/error-help/db/ora-00933/00933. 00000 -  "unexpected keyword at or near %s"
*Cause:    An unexpected keyword was encountered in the SQL statement at
           or near the position printed in the error message.
           One of the following occurred:
           1. You had a typo in your SQL statement.
           2. Unsupported syntax was encountered for a clause in the
           statement.
           3. An unsupported clause was encountered in the statement.
           4. A string was terminated prematurely leading to the rest
           of the string to be interpreted as keywords. For example, an
           apostrophe in the string may be causing it to
           end prematurely.
*Action:   Take the action that corresponds with the Cause
           1. Check that your SQL statement has no typos.
           2. Check Oracle Database documentation to find the
           correct syntax for the clause and update the problematic
           clause appropriately.
           3. Check Oracle Database documentation to find the correct
           syntax for the statement and remove the unsupported clause.
           4. Enter two single quotes instead of one to represent an
           apostrophe within a string.
*Params:   1) keyword_value
           keyword near the keyword causing the error. The keyword value
           may be truncated for readability if it is too long.
这个错误 ORA-00933: SQL 命令未正确结束 的原因是：Oracle 数据库不支持在一条 INSERT 语句中使用多个 VALUES 子句进行多行插入。

你的语法是其他数据库（如 MySQL、PostgreSQL、SQL Server）支持的多行插入语法，但 Oracle 不支持这种写法。
https://chat.deepseek.com/a/chat ... a-b56c-8ad0e5334e8b