sql-server - 从 Excel 在 SQL Server 数据库中运行 SQL 脚本(在 Excel 中预先生成)
问题描述
我生成了一个包含两个字段的 Excel 报告,ETA (Col H) 和供应商发票编号(Col I)。
电子表格用完了数据,我的客户只需要在这两个字段中进行更改。然后在 Col J 中,我有一个 Excel 字符串公式,它结合了各个字段,并创建了所需的 SQL 脚本。
目前,我的客户端从 Col J 复制所有文本,并将其粘贴到 SQL Server Management Studio 中,然后运行查询,从而更新 SQL 表。很简单。
但是,我想避免我的客户不得不在 SQL Server 中运行这些脚本。相反,我正在寻找建议或方法,他们可以从 Excel 中单击一个按钮,然后自动复制 Col J 并在 SQL Server 中运行,所有这些都来自 Excel(可能带有一个按钮,带有某种形式的宏)。
这可能吗?
下面是 Excel 报告的屏幕截图,显示在 col J 中自动生成的 SQL 查询。
请帮助我在 Excel 中构建此功能,也许是通过 VBA?
我希望我已经正确解释了我的要求?
谢谢!
解决方案
您可以创建一个文本文件并使用命令行实用程序 SQLCMD 运行查询,或者使用 ADODB 连接。
Option Explicit
Sub test()
Const METHOD = 1 '1=cmdsql 2=ADODB
Const SERVER = "test\sqlexpress"
Const DATABASE = "test"
Dim fso As Object, ts As Object, ar
Dim ws As Worksheet
Dim iLastRow As Long, i As Long
Dim sql As String, timestamp As String
Dim Folder As String, SQLfile As String, LOGfile As String
Dim t0 As String: t0 = Timer
' query file and log filenames
timestamp = Format(Now, "YYYYMMDD_HHMMSS")
Folder = ThisWorkbook.Path & "\"
SQLfile = timestamp & ".sql"
LOGfile = timestamp & ".log"
Set fso = CreateObject("Scripting.FileSystemObject")
' read data from sheet into array to build sql file
Set ws = ThisWorkbook.Sheets("Sheet1")
iLastRow = ws.Cells(Rows.Count, "J").End(xlUp).Row
If iLastRow = 1 Then
MsgBox "No data in Column J", vbCritical
Exit Sub
End If
ar = ws.Range("J2").Resize(iLastRow - 1).Value2
' connect to server and run query
If METHOD = 1 Then ' SQLCMD
' create sql file
Set ts = fso.CreateTextFile(SQLfile)
For i = 1 To UBound(ar)
sql = sql & ar(i, 1) & vbCr
Next
ts.write sql
ts.Close
' execute sql using slqcmd
Dim wsh As Object, sCommandToRun As String
Set wsh = VBA.CreateObject("WScript.Shell")
LOGfile = timestamp & ".log"
sCommandToRun = "sqlcmd -S " & SERVER & " -d " & DATABASE & _
" -i " & Folder & SQLfile & _
" -o " & Folder & LOGfile
wsh.Run sCommandToRun, 1, 1
MsgBox "See CMDSQL log file " & LOGfile, vbInformation, Format(Timer - t0, "0.0 secs")
ElseIf METHOD = 2 Then 'ADODB
Dim sConn As String, conn, cmd, n As Long
sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
";Initial Catalog=" & DATABASE & _
";Trusted_Connection=yes;"
' open log file
Set ts = fso.CreateTextFile(LOGfile)
' make connection
Set conn = CreateObject("ADODB.Connection")
conn.Open sConn
' execute sql statements
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = conn
For i = 1 To UBound(ar)
ts.writeLine ar(i, 1)
.CommandText = ar(i, 1)
.Execute
Next
End With
ts.Close
conn.Close
MsgBox UBound(ar) & " SQL queries completed (ADODB)", vbInformation, Format(Timer - t0, "0.0 secs")
End If
End Sub