从EXCEL到VBA脚本将多个值插入到SQL数据库中 [英] Inserting multiple values into a SQL database from EXCEL through VBA script

问题描述

只是尝试通过VBA脚本将数据从5个单元格插入到SQL Server 08数据库的列中。

所以基本上我有一个4列的表，我想将多组数据一次插入列中，这些数据将以下列结果将数据插入到数据库中。

 服务器名称中间件版本许可证
 TEST6测试1 1 
 TEST6 Testing1 1 1 
 TEST6 Testing2 1 1 
 TEST6 Testing3 1 1 
  / pre> 
 
 
我知道以下代码中的值不正确，但是当执行VBA脚本时，我会收到错误消息（vba代码下面）。 p> 
 
 
  Dim val1 As String，val2 As String，val3 As String，val4 As String 
 
 val1 = Range B126）值
 val2 =范围（C126）值
 val3 =范围（C127）值
 val4 =范围（D126）值
 
 conn.Open sConnString 
 
 Dim item As String 
 item4 =INSERT INTO [IndustrialComp]。[dbo]。[Middleware]（
 item4 = item4& [server_name]，[middleware]，[middlware]，[version]
 
 item4 = item4& ）值（
 item4 = item4&'& val1&'，'& val2&'，'& val3& &'）
 
 conn.Execute item4 
 
 End Sub 
  
消息264，级别16，状态1，行1 
列名称中间件在SET子句中被多次指定。不能在同一个SET子句中为一个列分配一个以上的值。修改SET子句以确保列只更新一次。如果SET子句更新视图的列，则列名middleware可能在视图定义中出现两次。
解决方案
我相信您在INSERT语句中指定的列是重复的，因此不正确。 
尝试：
  item4 = item4& [server_name]，[middleware]，[version]，[license]
  
更新：
您的SQL语句应如下所示：
  INSERT INTO [IndustrialComp]。[dbo]。[Middleware]（[server_name ]，[中间件]，[版本]，[许可证]）
 VALUES（'TEST6'，'Testing'，1,1）
，（'TEST6'，'Testing1'，1,1） 
，（'TEST6'，'Testing2'，1,1）
，（'TEST6'，'Testing3'，1,1）
  
所以你必须在你要插入的每一行的括号之间重复一下。
 
 
 
 但是，您现在只有4个变量在您的解决方案中包含4个不同的值，因此您将无法插入4个不同的行，因为您只能选择单元格B126，C126，C127和D126中的值。这可能是您要插入的第一行？还是要自己添加1,2,3到测试并重复其他值？ 
请相应地解释并更新您的答案。
 
Just trying to insert data from 5 cells via a VBA script, into a column on an SQL server 08 database.

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..
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.
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.
解决方案
I believe the columns you specify in your INSERT statement are duplicated and therefore not correct.
Try:
item4 = item4 & "  [server_name],[middleware],[version],[license]"
Update:
Your SQL statement should look like this:
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 Testing yourself and repeat the other values?
Please explain and update your answer accordingly.

                        
这篇关于从EXCEL到VBA脚本将多个值插入到SQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！