vba 通过VBA脚本从EXCEL向SQL数据库中插入多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20246117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting multiple values into a SQL database from EXCEL through VBA script
提问by user3013325
Just trying to insert data from 5 cells via a VBA script, into a column on an SQL server 08 database.
只是试图通过 VBA 脚本将 5 个单元格中的数据插入到 SQL server 08 数据库的列中。
So basically I have 1 table with 4 columns, I want to insert multiple sets of data into the columns at once which would insert data into the DB with the below result..
所以基本上我有 1 个有 4 列的表，我想一次将多组数据插入到列中，这会将数据插入到数据库中，结果如下..
Server Name Middleware Version License
TEST6 Testing 1 1
TEST6 Testing1 1 1
TEST6 Testing2 1 1
TEST6 Testing3 1 1
I know the values are not correct on the below code, but I get the error message (below the vba code) when the VBA script is executed.
我知道以下代码中的值不正确，但是在执行 VBA 脚本时我收到错误消息（在 vba 代码下方）。
Dim val1 As String, val2 As String, val3 As String, val4 As String
val1 = Range("B126").Value
val2 = Range("C126").Value
val3 = Range("C127").Value
val4 = Range("D126").Value
conn.Open sConnString
Dim item As String
item4 = "INSERT INTO [IndustrialComp].[dbo].[Middleware]("
item4 = item4 & " [server_name],[middleware],[middlware],[version]"
item4 = item4 & " )Values("
item4 = item4 & " '" & val1 & "', '" & val2 & "', '" & val3 & "','" & val4 & "')"
conn.Execute item4
End Sub
Msg 264, Level 16, State 1, Line 1 The column name 'middleware' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'middleware' may appear twice in the view definition.
消息 264，级别 16，状态 1，第 1 行 在 SET 子句中多次指定列名称“中间件”。不能在同一个 SET 子句中为一列分配多个值。修改 SET 子句以确保一个列只更新一次。如果 SET 子句更新视图的列，则列名“中间件”可能会在视图定义中出现两次。
回答by NickyvV
I believe the columns you specify in your INSERT statement are duplicated and therefore not correct. Try:
我相信您在 INSERT 语句中指定的列是重复的，因此不正确。尝试：
item4 = item4 & " [server_name],[middleware],[version],[license]"
Update: Your SQL statement should look like this:
更新：您的 SQL 语句应如下所示：
INSERT INTO [IndustrialComp].[dbo].[Middleware]([server_name],[middleware],[version],[license])
VALUES ('TEST6','Testing',1,1)
,('TEST6','Testing1',1,1)
,('TEST6','Testing2',1,1)
,('TEST6','Testing3',1,1)
So you have to repeat the block between parenthesis for every row you want to insert.
因此，您必须为要插入的每一行重复括号之间的块。
However, you now only have 4 variables that hold 4 different values in your solution, so you will never be able to insert those 4 different rows because you only select values in cells B126, C126, C127 and D126. That will likely be the first row that you want to insert? Or do you want to add the 1,2,3 to Testingyourself and repeat the other values?
Please explain and update your answer accordingly.
但是，现在您的解决方案中只有 4 个变量包含 4 个不同的值，因此您将永远无法插入这 4 个不同的行，因为您只选择单元格 B126、C126、C127 和 D126 中的值。这可能是您要插入的第一行？或者您想将 1,2,3 添加到Testing自己并重复其他值？请相应地解释并更新您的答案。
回答by Steve Gon
I am assuming the data is in Excel. If so, just loop through the rows. Also, it's seems that your val1, val2 etc. don't match the example. Maybe you meant val3 to be D126 and val4 to be E126. I will assume that. Here is the corrected code:
我假设数据在 Excel 中。如果是这样，只需遍历行。此外，您的 val1、val2 等似乎与示例不匹配。也许你的意思是 val3 是 D126，val4 是 E126。我会假设。这是更正后的代码：
Dim sSQL as string
Dim i as long
i=0
while ActiveSheet.Range("B126").offset(i,0).value <> "" 'stop when there is a blank cell
i=i+1
conn.Open sConnString
sSQL = "INSERT INTO [IndustrialComp].[dbo].[Middleware]("
sSQL = sSQL & " [server_name],[middleware],[version],[license]"
sSQL = sSQL & " )Values ("
sSQL = sSQL & " '" & ActiveSheet.Range("B126").offset(i,0).Value & "', "
sSQL = sSQL & " '" & ActiveSheet.Range("C126").offset(i,0).Value & "', "
sSQL = sSQL & " '" & ActiveSheet.Range("D126").offset(i,0).Value & "', "
sSQL = sSQL & " '" & ActiveSheet.Range("E126").offset(i,0).Value & "' "
sSQL = sSQL & ")"
conn.Execute sSQL
wend
Code not tested but it compiles.
代码未经测试但可以编译。
回答by Jonathan Willcock
If you have multiple rows of data, it is much more efficient to insert all the rows in one go in a single call to a stored procedure. To do this you serialize your data into xml and then call a stored procedure which takes xml as a string parameter.
如果您有多行数据，在对存储过程的一次调用中一次性插入所有行会更有效率。为此，您将数据序列化为 xml，然后调用将 xml 作为字符串参数的存储过程。
AFAIK you cannot serialize a Dictionary, but you can serialize a List.
AFAIK 你不能序列化一个字典，但你可以序列化一个列表。
So I suggest you loop through your Dictionary (List of Dictionaries) filling a List with appropriate values. I tend to use Structures for this, but simple classes work fine as well, e.g.:
所以我建议你遍历你的字典（字典列表），用适当的值填充一个列表。我倾向于为此使用结构，但简单的类也可以正常工作，例如：
Public Class DBData
Public pKey As Integer
Public pValue As Double
Public Sub New(key As Integer, val As Double)
pKey = key
pValue = val
End Sub
Public Sub New()
pKey = 0
pValue = 0.0
End Sub
End Class
Create your List and fill it e.g:
创建您的列表并填写它，例如：
Dim myList As New List(Of DBData)
For Each kvPair In dict
myList.Add(New DBData(kvPair.Key, kvPair.Value))
Next
Now for the bit of magic to serialize:
现在要序列化一些魔法：
Dim sw As New StringWriter
Dim serializer As New XmlSerializer(GetType(List(Of DBData)))
Dim ns As New XmlSerializerNamespaces()
serializer.Serialize(sw, myList)
Dim xml As String
xml = sw.ToString
Dim pos As Integer
pos = xml.IndexOf("<Array")
xml = xml.Substring(pos)
Notice here I am stripping off the header of the xml that has just been created. That is because I have never been able to get SQL Server to work if the header was included - you probably can, I've just never looked hard enough.
请注意，我正在剥离刚刚创建的 xml 的标题。那是因为如果包含标头，我永远无法让 SQL Server 工作 - 你可能可以，我只是从来没有仔细看过。
We call the stored procedure like this (obviously change the connection string to one that works for you):
我们像这样调用存储过程（显然将连接字符串更改为适合您的字符串）：
Using conn As New SqlConnection("Integrated Security=true; Initial Catalog=dbname; Data Source=servername")
Using cmd As New SqlCommand("uspDBDataInsert", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter
param = cmd.CreateParameter
param.ParameterName = "@dbdata"
param.DbType = DbType.String
param.Value = xml
cmd.Parameters.Add(param)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
As to SQL Server the procedure required looks like this:
对于 SQL Server，所需的过程如下所示：
CREATE PROCEDURE [dbo].[uspDBDataInsert]
-- Add the parameters for the stored procedure here
@dbdata varchar(MAX)
AS
BEGIN try
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idoc int
--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @dbdata
insert into DBData
(
id, rowvalue
)
select
pKey,pValue
FROM OPENXML (@idoc, '/ArrayOfDBData/DBData',2)
WITH (
pKey int,
pValue float
)
select @@ROWCOUNT
end try
begin catch
declare @msg nvarchar(200)
SELECT @msg = ('Error during insert of DBData...')
end catch
Obviously here you will need to change the ArrayOf to whatever you call your class and of course use your own table.
显然，在这里您需要将 ArrayOf 更改为您对类的任何称呼，当然还需要使用您自己的表。
Although I have shown here just a simple key/value pair, you can populate a multi-column table in exactly the same way.
虽然我在这里只展示了一个简单的键/值对，但您可以以完全相同的方式填充多列表。
One point to note: your class/structure must be Public in a Public module, otherwise serialize will not work. Also if you provide your own parameter constructor for a class (as I do), you must also provide a parameterless one, otherwise again the serializer will object.
需要注意的一点：您的类/结构必须在 Public 模块中是 Public，否则序列化将不起作用。此外，如果您为一个类提供自己的参数构造函数（就像我所做的那样），您还必须提供一个无参数的构造函数，否则序列化程序将再次反对。
HTH
HTH
Addendum
附录
What I forgot to put, for the VB to work you need:
我忘了说，要使 VB 工作，您需要：
Imports System.IO
Imports System.Xml.Serialization
Imports System.Data.SqlClient
