Insert multiple rows using one insert statement in Access 2010

Question

I want to insert multiple values into an Access 2010 table, but I can't seem to find a way.
MySQL had a nice way:

INSERT INTO Production.UnitMeasure
VALUES 
    (N'FT2', N'Square Feet ', '20080923'),
    (N'Y', N'Yards', '20080923'),
    (N'Y3', N'Cubic Yards', '20080923');

Can something like this be done in SQL Server too?

Answer 1

Use this confirm working query:

INSERT INTO Product (Code,Name,IsActive,CreatedById,CreatedDate )

SELECT * FROM 
(    
  SELECT '10001000' AS Code,
         'Blackburn sunglasses' AS Name,
         1 AS IsActive,
         1 AS CreatedById,
         '2/20/2015 12:23:00 AM' AS CreatedDate 
   FROM Product    
   UNION 
   SELECT '10005200' AS Code,
          '30 panel football' AS Name,
          1 AS IsActive,
          1 AS CreatedById,
          '2/20/2015 12:23:09 AM' AS CreatedDate 
    FROM Product

) ;

Answer 2

I know its late to answer, but there are a couple of methods which are still useful today, (not mentioned here). There are two general methods.

---

1. Loop through using a VBA script with the 'Docmd.RunSQL' statement. - This is generally quite slow especially as the number of rows increases but easy to understand.

2. Copy your 'data array' into an excel worksheet and then use a database query link to the excel file (my preferred method) - Benefits of this is it is almost as fast as if the table was already in the database and not necessarily slowed down by the number of records as the previous method - slightly slower than the above when you have a small number of records however

---

The Docmd Method:

Option Compare Database
Option Base 1

'------- method created by Syed Noshahi --------
'https://www.linkedin.com/in/syed-n-928b2490/


Sub DoCmdRoutine()

Dim arr() As Variant    ' create an unsized array ready for data

'--------------For the purposes of the Example, put some data in the array----------

ReDim arr(5, 5)
For i = LBound(arr) To UBound(arr)
    For j = LBound(arr) To UBound(arr)
        arr(i, j) = i * j
        If i = LBound(arr) Then arr(i, j) = "col_" & arr(i, j) 'Append "col_" before the column names
    Next
Next
    

'-------------Importing the data to a table in MS ACCESS----------------------------

sSQL = "INSERT INTO [#TableTemp] "  ' change table to actual table name

DoCmd.SetWarnings False             'turn warnings off - no popups!

For i = 2 To UBound(arr)            'start at 2 assuming that the first columns are headers
    vals = ""                       'go through each column and copy the data to SQL string format
                                    'replace any single quote with double quotes so it does not error importing into SQL
    For j = 1 To UBound(arr, 2)
        If IsDate(arr(i, j)) Then           'if a date, convert to a number and let access re-covert to date (best chance at success)
            vals = vals & " cdate('" & CDbl(arr(i, j)) & "'),"
        ElseIf IsNumeric(arr(i, j)) Then    'if a number put through as a number
            vals = vals & arr(i, j) & ","
        Else                                'otherwise treat as a text value
            vals = vals & Replace(arr(i, j), "'", "''", , , 1) & "',"
        End If
    Next
    
    vals = " VALUES(" & Left(vals, Len(vals) - 1) & ")"     'put in correct sql format
    DoCmd.RunSQL sSQL & vals        'Run the SQL statement and import into the database
Next

DoCmd.SetWarnings True             'turn warnings on

End Sub

---

The Excel Link Method:

Option Compare Database
Option Base 1

'------- method created by Syed Noshahi --------
'https://www.linkedin.com/in/syed-n-928b2490/

Sub ExcelLinkRoutine()

Dim arr() As Variant    ' create an unsized array ready for data
Dim oExcel As Object    ' Excel instance - late binding
                        ' works with access 2007+, access 2003 has a different SQL syntax

'--------------For the purposes of the Example, put some data in the array----------

ReDim arr(5, 5)
For i = LBound(arr) To UBound(arr)
    For j = LBound(arr) To UBound(arr)
        arr(i, j) = i * j
        If i = LBound(arr) Then arr(i, j) = "col_" & arr(i, j) 'Append "col_" before the column names
    Next
Next

    
'----------------------------output the array to an excel file ---------------------

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add 1
Set wb = oExcel.ActiveWorkbook

'network file path & normal path examples below
'fileNameWithExtention = "\\networkpath\location\example999.xlsb"           ' note that xlsb file format must be used
                                                                            ' other formats can be used by changing 'xlExcel12'
                                                                            ' ONLY change the path not the FILE NAME
fileNameWithExtention = "C:\Users\public\documents\example999.xlsb"         ' same as above

checkFileExists = Dir(fileNameWithExtention)
If Len(checkFileExists) > 0 Then
    'only delete the file if its called example999!
    If checkFileExists = "example999.xlsb" Then
        Kill fileNameWithExtention
    End If
End If

With wb
    .Sheets(1).Cells(1, 1).Resize(UBound(arr), UBound(arr, 2)).Value2 = arr()
    .SaveAs fileNameWithExtention, 50                                       ' 50 means xlExcel12
    .Close False
End With

Set wb = Nothing
Set oExcel = Nothing

'------------ Importing the data to a table in MS ACCESS-----------------------------

    'NOTE1: The saved down excelfile MUST be named Sheet1
    'NOTE2: if the file path contains special characters such as ,-'
    '       you may need find the correct way to input (or remove the special chars)

sSQL = "SELECT T1.* INTO [#TableTemp] "  ' change table to actual table name
sSQL = sSQL & " FROM [Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & fileNameWithExtention & "].[Sheet1$] as T1;" ' linked table format


DoCmd.SetWarnings False             'turn warnings off - no popups!
DoCmd.RunSQL sSQL                   'Run the SQL statement and import into the database
DoCmd.SetWarnings True              'turn warnings on


End Sub 

---

OUTPUT:

Col_1	Col_2	Col_3	Col_4	Col_5
2	4	6	8	10
3	6	9	12	15
4	8	12	16	20
5	10	15	20	25