vba 将 Excel 中的数据插入 SQL DB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3767879/
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
INSERT data from Excel into SQL DB
提问by Ben
I have created an Excel Sheet that does some lookups to format data that needs to be inserted into another table. This Excel Workbook needs to be given to some users that are free to add some new rows, and then need to be able to hit an "Insert Into Database" button and have the records transformed and inserted as new records into a SQL Table. I am using Excel 2010 and SQL Server 2008. I have a connection to the DB as I am using it to pull some data back in order to verify the new rows being added, but I'm not sure how to then insert the data back.
我创建了一个 Excel 工作表，它执行一些查找以格式化需要插入到另一个表中的数据。这个 Excel 工作簿需要提供给一些用户，这些用户可以自由添加一些新行，然后需要能够点击“插入数据库”按钮并将记录转换并作为新记录插入 SQL 表中。我正在使用 Excel 2010 和 SQL Server 2008。我有一个到数据库的连接，因为我使用它来拉回一些数据以验证正在添加的新行，但我不知道如何然后插入数据.
回答by Fionnuala
You can do a lot with ADO:
您可以使用 ADO 做很多事情：
Dim cn As New ADODB.Connection
''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s
You can also use the ACE connection: http://www.connectionstrings.com/or OPENROWSET and an SQL Server connection. In all cases, you may have problems with mixed data types in columns, depending on your registry settings (http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669)
您还可以使用 ACE 连接：http: //www.connectionstrings.com/或 OPENROWSET 和 SQL Server 连接。在所有情况下，列中的混合数据类型可能会出现问题，具体取决于您的注册表设置（http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669）
回答by Ben
I have found out that within a macro, you can create an ADO connection by adding a reference to the "Microsoft ActiveX Data Objects 6.0 Library". Once you have opened a connection within the Macro, you can create your insert statement and execute it via using the connection.Execute(statement) method:
我发现在宏中，您可以通过添加对“Microsoft ActiveX 数据对象 6.0 库”的引用来创建 ADO 连接。在宏中打开连接后，您可以创建插入语句并通过使用 connection.Execute(statement) 方法执行它：
Dim item as String = "Insert Into MyTable(ColA,ColB) VALUES('Foo', 'Bar')"
Dim thisCon As New ADODB.Connection
thiscon.Open("ConnectionString")
thisCon.Execute (item)
回答by Zoitc2014
After modifying the data in excel, need to generate Update statements, which will be executed by pressing the button "update". As a result, will be executed Update and Insert statements. Then have to send a query to refresh the data in Excel.(imho)
在excel中修改数据后，需要生成Update语句，点击“update”按钮执行。结果，将执行Update 和Insert 语句。然后必须发送查询以刷新 Excel 中的数据。（恕我直言）
