Pass rows from Excel file to stored procedure parameters-CodePudding

I need help with SQL query that would load data from Excel and pass them to stored procedure. Excel file contains 4 columns and 50k rows, while stored procedure expects 4 parameters. How can I iterate through Excel file row by row and pass this data to the stored procedure? I am using SQL Server 2016.

CodePudding user response：

Thanks all. I think I will try SSIS option using OLE DB Command which executes SQL command for each row in the Data Flow. In my case it would be a parameterized call to stored procedure.

CodePudding user response：

Your best bet is probably to run a SProc in SQL Server, and control it from Excel. See the code example below for details on how to do this.

Sub RunSProc()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String

Set cn = New ADODB.Connection

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Data Source=your_server_name;"
strConn = strConn & "Initial Catalog=your_database_name;"
strConn = strConn & "Integrated Security=SSPI;"

cn.Open strConn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "MyOrders"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Range("E1").Text
cmd.Parameters(2).Value = ActiveSheet.Range("E2").Text
Set rs = cmd.Execute()

If Not rs.EOF Then
    Worksheets("sheet2").Range("A5:D500").CopyFromRecordset rs
    rs.Close
End If

End Sub