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.

If it's in excel, the work is done for you. Just format it in excel and copy/paste it into ssms. — dfundako
– dfundako, Commented Dec 27, 2016 at 20:28
@Comintern I guess i mean fields. I would be inserting each individual cell as a value. — SteveG
– SteveG, Commented Dec 27, 2016 at 20:32
@dfundako I know that I can just copy and paste that, However this is going to be part of a program that runs whenever someone wants to update a table. So this would be automating that process, — SteveG
– SteveG, Commented Dec 27, 2016 at 20:33

Comintern · Accepted Answer · 2016-12-27 21:16:35Z

4

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.

answered Dec 27, 2016 at 21:16

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

SteveG Over a year ago

This seems to make things a lot easier and i will have to try to use this method in other projects I have worked on. However I am getting an error.

SteveG Over a year ago

The error says: Parameter object is improperly defined. Inconsistent or incomplete information was provided.

Comintern Over a year ago

@SteveG - I'm not surprised at all, this isn't really complete code (just an illustration of the concept) - you also need to supply the parameter type and the specify that it's an input value. See Documentation for a more detailed sample of how to build a parameter.

SteveG Over a year ago

For param1 - param121 would I need to create parameters for each one?

Comintern Over a year ago

@SteveG - Yep. I usually automate building insert commands by reading the table schema and using that to build both the sql and parameters. There's an example of reading the schema here.

|

Jake · Accepted Answer · 2016-12-27 21:02:23Z

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:

In SQL Server Management Studio, login to your database server.
Right click the database you want to import data into and select Task -> Import Data Import Data Image
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
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.
On the Specify Table Copy or Query window, select Copy data from one or more table or views. Click Next.
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.
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.
Click Next. The import should run immediately.
Review the summary page and click Finish.
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.

answered Dec 27, 2016 at 21:02

Jake

4011 gold badge4 silver badges13 bronze badges

2 Comments

Jake Over a year ago

Well, now that you've posted VBA code, this is mostly meaningless. I would suggest doing a parameterized query or concatenating the cells as strings in each row of the spreadsheet, adding comma delimiters between each cell read, and placing each row into an array.

Jake Over a year ago

I would then iterate through the string array and execute the INSERT INTO statement for each string in the array. However, be mindful that this can lead you open to SQL injection if the dynamic query isn't parameterized.

Collectives™ on Stack Overflow

Loop to insert multiple values into SQL Server

2 Answers 2

16 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related