0

I created a stored procedure in SQL Server for testing purposes like this:

USE [My_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[INSERT_PRODUCT]
    @id             int,
    @Product_Name   nvarchar(255),
    @Price          money,
    @Quantity       int
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO My_Table (id, Product_Name, Price, Quantity)  
    VALUES (@id, @Product_Name, @Price, @Quantity)
END

It's working fine...

Then, I create a VBA code that connect with my_DB in SQL Server and fill the columns as follow:

Public Sub Send_to_DB()

    Dim Conn As ADODB.Connection
    Dim strConn As String
    Dim Cmd As ADODB.Command

    Set Conn = New ADODB.Connection
    strConn = "Provider=My_Provider; Data Source=My_Server;" _
             & "Initial Catalog=My_DB;Integrated Security=SSPI;"
    Conn.Open strConn
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = Conn
        .CommandText = "INSERT_PRODUCT"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 0
        .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , 16)
        .Parameters.Append .CreateParameter("@Product_Name", adChar, adParamInput, 255, "Essaii")
        .Parameters.Append .CreateParameter("@Price", adCurrency, adParamInput, , 155)
        .Parameters.Append .CreateParameter("@Quantity", adInteger, adParamInput, , 9)
        ' repeat as many times as you have parameters

        .Execute
    End With

End Sub

My output is a row in SQL Server stating id=16 / Product_Name= Essaii / Price=155 / Quantity= 9.

Ok fine that's cool my try worked. Now I have to insert the same way a big Excel table (approx 50,000 rows :) ) in My_DB in SQL Server.

Should I loop each row? If so, should I create a table object where I store my Excel table and loop through it?

I would really appreciate a bit of help here !

Thank you so much in advance for helping me to sort this out.

Peace,

Thom A
  • 88,727
  • 11
  • 45
  • 75
Pierre_CM
  • 55
  • 1
  • 8
  • Mass load the data into a variant array and loop that. Looping the actual range is slow. – Scott Craner Jun 12 '20 at 15:59
  • I doubt that looping over Excel is slowing the process down very much compared to all the insert-statements... If this is a one-time or batch process, I wouldn't mind much about speed anyway. And I don't see a point in creating a table. – FunThomas Jun 12 '20 at 16:04
  • If the format of the Excel table matches exactly to the SQL table you should be able to import it directly without VBA using SSMS – HackSlash Jun 12 '20 at 16:09
  • Does this answer your question? [How to import an Excel file into SQL Server?](https://stackoverflow.com/questions/39610133/how-to-import-an-excel-file-into-sql-server) – HackSlash Jun 12 '20 at 16:10
  • Hi guy's thanks for your help, it shoud be a process actually. The actual file is under excel format, updated and arriving every morning so every morning the excel should load the data into the SQL server automatically. I don't think the SSMS wizard can do that so I assume I have to write a macro that triggers the stored procedure... – Pierre_CM Jun 12 '20 at 17:02

0 Answers0