Fastest Way to Upload Excel Sheet to MSSQL
Posted on 2012-09-05
Okay, I'm trying to upload 300,000+ records using Excel VBA into SQL and I don't want to have to loop through the records one at a time as this takes incredibly too long, I would instead like to reference the sheet in my query somehow to speed the process up.
I was trying to reference a named range or address but I'm not sure how to properly pass the range / values into the query without looping through the records :S
Dim Connection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Set RecordSet = New ADODB.RecordSet
'Open the Connection to Server
Set Connection = New ADODB.Connection
Connection.Open "Driver=SQL Server;Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & FC_UserName & ";Pwd=" & FC_Password & ";"
Connection.CommandTimeout = "99999999"
Dim DataRange As Range
Set DataRange = ThisWorkbook.Sheets("Data").Range("A2:AE42")
DataRange.Name = "TempRange"
RecordSet.Open "INSERT INTO [SERVER].[dbo].[TABLENAME] SELECT * FROM [TempRange]", Connection