vba 将 Excel 单元格值插入现有的 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8432740/
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 Excel Cell values into an existing SQL table
提问by Jorg Ancrath
Possible Duplicate:
Excel VBA INSERT INTO statement using variables
I've just started learning a bit of VBA, I've managed to import data from a DB created in SQL Server just fine, now I'm trying to export data from Excel into the same DB.
我刚刚开始学习一些 VBA，我已经设法从 SQL Server 中创建的数据库导入数据就好了，现在我正在尝试将数据从 Excel 导出到同一个数据库中。
The following works just fine:
以下工作正常：
Dim cnState As ADODB.Connection
Dim sSQL As String
Set cnState = New Connection
With cnState
.Provider = "SQLOLEDB"
.ConnectionString = "server=.\SQLEXPRESS; Trusted_Connection=Yes; database=SIM_PROJ"
.Open
End With
sSQL = "Insert Into Alunos Values ('3', 'Tiago Aleixo', 'Rua dos Pincéis' , '1990-05-26' , 'M' , 'Multimédia' , 'D')"
cnState.Execute sSQL
End Sub
Private Sub btn_goMenu_Click()
Sheet1.Select
The only issue is, I've only managed to figure out how to insert actual values through the code, I want to be able to read a whole line in Excel and insert it into my DB.
唯一的问题是，我只设法弄清楚如何通过代码插入实际值，我希望能够在 Excel 中读取整行并将其插入到我的数据库中。
Something like this:
像这样的东西：
sSQL = "Insert Into Alunos Values (Range(A4).Value, Range(B4).Value, Range(C4).Value, Range(D4).Value, Range(E4).Value, Range(F4).Value, Range(G4).Value)"
IE, I only want values from line 4 to be inserted.
IE，我只想插入第 4 行的值。
回答by Levin Magruder
Probably you have to build the string more incrementally like:
可能您必须更渐进地构建字符串，例如：
SQL = "INSERT INTO alunos VALUES ('" + Range(A4).Value + "', '" + Range(A5).Value + "', '"...
The syntax you show looks ilke it would try to insert the string "Range(A4).Value" into the database.
您显示的语法看起来像它会尝试将字符串“Range(A4).Value”插入到数据库中。
回答by UY420
Save your file as comma delimited CSV file and then you can use bulk insert as below. declare @sql varchar(max) set @sql = "BULK INSERT TAB_UY420 FROM '"+@File+"' WITH (FIELDTERMINATOR = ',') " exec (@sql)
将您的文件保存为逗号分隔的 CSV 文件，然后您可以使用如下批量插入。声明@sql varchar(max) set @sql = "BULK INSERT TAB_UY420 FROM '"+@File+"' WITH (FIELDTERMINATOR = ',') " exec (@sql)
