Loop to insert multiple values into SQL Server

Question

I am trying to insert a lot of values into SQL Server in one SQL insert statement. So for example..

Insert Into someDataBase
Values(value1, value2, value3,..., value120, value121)

All these values will be in the correct order on a sheet in Excel. I already created a loop to go through all the 121 values but I can't think of a loop for the inserting the values without writing each value individually.

Can anyone help me out?

My code looks like this:

Sub AddRows()
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String


Dim comm As ADODB.Command
Set comm = New ADODB.Command
Dim param As ADODB.Parameter
Dim getType As String

SQLStr = "Insert Into [DB] Values("
For i = 1 To 121
    'Get type
    getType = CellType(i, Cells(2, i))
    SQLStr = SQLStr & "?,"
    Set param = New ADODB.Parameter
    param.Name = "Param" & i
    Set param = comm.CreateParameter("param" & i, getType, adParamInput, 1000)
    param.Attributes = adParamNullable
    Debug.Print (param.Type)
    comm.Parameters.Append param
Next i
SQLStr = Left(SQLStr, Len(SQLStr) - 1) & ");"

Set Cn = New ADODB.Connection
Cn.CommandTimeout = 0
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

comm.ActiveConnection = Cn
comm.CommandText = SQLStr
comm.CommandType = adCmdText


Dim test As String
lastrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
'For x = 2 To lastrow
    For i = 1 To 121
        Debug.Print (Cells(2, i).Value)
        comm.Parameters.Item("Param" & i).Value = Cells(2, i).Value
    Next
    comm.Execute
'Next x

Cn.Close
Set Cn = Nothing

End Sub

This is a portion of my CellType Function:

Private Function CellType(Num) As MultipleValues
Application.Volatile
CellType.CellNum = 50
Select Case True
    Case Num = 1
        CellType.CellType = adInteger
    Case Num = 2
        CellType.CellType = adInteger
    Case Num = 3
        CellType.CellType = adVarWChar
        CellType.CellNum = 255
    Case Num = 4
        CellType.CellType = adCurrency
    Case Num = 5
        CellType.CellType = adVarWChar
        CellType.CellNum = 255

The values for the first 5 values are: 0, null, 6071/1, 44.5, Biltmore Fleur De Lis Collection Authentic Wrought Iron

It breaks on the second value which is a null but should be an integer.

Answer 1

I would generally agree that using an SSIS package would be the best way to approach this, but if you want to do it through code, it's not difficult with a parameterized query in ADODB:

Use the ? placeholder for parameters when you're building your INSERT statement, and add parameters to an ADODB.Command object:

Dim comm As ADODB.Command
Set comm = New ADODB.Command
Dim param As ADODB.Parameter

SQLStr = "Insert Into [db] Values("
For i = 1 To 121
    SQLStr = SQLStr & "?,"
    Set param = New ADODB.Parameter
    param.Name = "Param" & i
    comm.Parameters.Append param
Next i
SQLStr = Left(SQLStr, Len(SQLStr) - 1) & ");"

When you're done, you'll have a matched set of parameters and placeholders. Then open your connection (or do it before this, it really doesn't make much difference) and set up the command:

comm.ActiveConnection = Cn
comm.CommandText = SQLStr
comm.CommandType = adCmdText

Finally, loop through your rows for each record, loop through the values and assign them to parameters, and execute for each row:

lastrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
For x = 2 To lastrow
    For i = 1 To 121
        comm.Parameters.Item("Param" & i).Value = Cells(x, i).Value
    Next
    comm.Execute
Next x

Note that ADODB Commands are optimized to be looped over - the backend treats this more like a batch insert, so there isn't really much point in trying to build a gigantic insert will all the records in it at once.

Answer 2

Don't know if the excel spreadsheet has special formatting, but if its in a CSV format, you can use SQL Server Import and Export Wizard to do this. Here's how:

1. In SQL Server Management Studio, login to your database server.
2. Right click the database you want to import data into and select Task -> Import Data Import Data Image
3. On the Choose Data Source screen, choose Microsoft Excel from the Data Source drop down. Enter the file path to the excel spreadsheet in Excel file path. Select the appropriate Excel version in Excel version. If the first row in the spreadsheet contains the database column names, check the First row has column names. If it's just data, uncheck it. Choose Data Source Image
4. Click Next and choose Microsoft OLE DB Provider for SQL Server in the Destination drop down. Select your sql server name in Server name drop down. Choose Authentication option. Click Refresh, then choose the database you want to insert the Excel data into.
5. On the Specify Table Copy or Query window, select Copy data from one or more table or views. Click Next.
6. Select the check box next to Sheet1$ in the Source column. Click in the Destination column and select the table you want to import the Excel data into. Clicking the Preview button will show the query and how the data will look in the new table. Click Next.
7. The mapping should be good on the Review Data Type Mapping page. This window allows you to setup options on what SQL should do in regards to converting. Defaults should be fine. Click Next.
8. Click Next. The import should run immediately.
9. Review the summary page and click Finish.
10. The import will perform several operations. Green check marks indicate that the operation was successful. Read circles with X's indicate failure. If you get a failure, post an image of it here with your data and the hyperlinked message from the Message column.

If you want, you can check out the bulk insert command as well on MSDN. I don't have enough rep to post more images. I'll finish it later when I have enough.