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?

Bisque

Which is it? Access 2010 or SQL Server? – Inaccuracy

Access 2010 .Does the syntax differ? – Bisque

Yes, the syntax differs as well as the capabilities. See the answer(s) below. – Inaccuracy

Re: MS Access - Yes and no - #63004 – Perilune

Answer 1

For SQL-Server: Yes, and it can exactly like you write. Just be certain that the column values are in the same order as they appear in the table. Also: you must supply a value for each existing column.

For Access 2010: No. At least not by hardcoded values in the sql, but only by selecting multiple records from a table (in the same or in another database). See also the link in the answer of Khepri.

Inaccuracy

Yes - as of SQL Server 2008 - this wasn't valid before the 2008 version – Exaction

This answer is not completely true. – Tuantuareg

All I needed to know was, can this be done in Access. This answers says no. Good enough for me! – Bisque

It's more of a yes and no for MS- Access see this answer: #63004 – Perilune

Answer 2

As marc_s has pointed out, for SQL Server 2008 and later, you can just use table value constructors. For previous versions, you can use insert and select...union all, e.g.:

INSERT INTO Production.UnitMeasure
SELECT N'FT2',N'Square Feet ','20080923' union all
SELECT N'Y',  N'Yards',       '20080923' union all
SELECT N'Y3', N'Cubic Yards', '20080923'

(Specific documentation on Table Value Constructors in SQL Server. I can't find specific separate documentation on row value constructors, but that's what they are)

Ferrochromium

Answer 3

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

) ;

Curare

If the inserted columns already match the internal order of the table, the column names and aliases are not necessary. This can significantly simplify / shorten a long statement. This may go against the principle that an SQL engine could return columns and rows in random orders, but it works in Access. But even if the INTO clause keeps the column names, only the first SELECT statement in the UNION needs column aliases since all subsequent unions will adopt the same names. This will still help in shortening a very long insert with many rows. – Acea

Combining @Curare and @C Perkins ideas. I found that I must have the column names in the beginning and the first select. To help shorten the statement, I created a new, empty table with the same the structure and used that in my alias. Here TT is an empty table with the same fields as TABLE_T. INSERT INTO TABLE_T (FIELD1, FIELD2, FIELD3) SELECT * FROM ( SELECT "EE" AS FIELD1, 0 AS FIELD2, 100 AS FIELD3 from TT union SELECT "RR" , 0, 200 from TT union SELECT "FF" , 0, 200 from TT union SELECT "GG" , 0, 200 from TT ) – Virology

Answer 4

Create a table called OneRow with a single integer column. Insert one row.

Then:

INSERT INTO Production.UnitMeasure
SELECT 'FT2', 'Square Feet ', '20080923' FROM OneRow
UNION ALL SELECT 'Y', 'Yards', '20080923' FROM OneRow
UNION ALL SELECT 'Y3', 'Cubic Yards', '20080923' FROM OneRow

Your exact syntax works on SQL Server 2008. For earlier use my above query without the FROM clauses and no helper table.

Tuantuareg

Answer 5

SQL Server definitely allows this: EDIT: [As of SQL Server 2008, thank you Marc_s]

INSERT INTO [Table]
([COL1], [COL2])
VALUES
('[email protected]', 1),
('[email protected]', 2)

As for the Access requirement, I'm no access guru but I found this MSDN documentation that shows how to do multiple inserts at once.

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]
     SELECT [source.]field1[, field2[, …] FROM tableexpression

Doing some cursory reading beyond this, you can use a "dummy" from table if all of your values are known ahead of time as in your example.

Bibliopole

As of SQL Server 2008 - yes; before that, this was not possible – Exaction

Using a dummy table (which still has to exist) allows you to insert only one record, because the UNION ALL trick Damien mentioned isn't permitted. In that case you have also to limit the result by using TOP 1. Please let me know when I'm wrong. – Inaccuracy

@ngln Use a dummy table with only one row in it such as OneRow. – Tuantuareg

Answer 6

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

Gismo

Answer 7

MS Access does not allow multiple insert from same sql window. If you want to insert, say 10 rows in table, say movie (mid, mname, mdirector,....), you would need to open the sql windows,

1. type the 1st stmt, execute 1st stmt, delete 1st stmt
2. type the 2nd stmt, execute 2nd stmt, delete 2nd stmt
3. type the 3rd stmt, execute 3rd stmt, delete 3rd stmt ......

Very boring. Instead you could import the lines from excel by doing:

1. Right-click on the table name that you have already created
2. Import from Excel (Import dialog box is opened)
3. Browse to the excel file containing the records to be imported in the table
4. Click on "Append a copy of the records to the table:"
5. Select the required table (in this example movie)
6. Click on "OK"
7. Select the worksheet that contains the data in the spreadsheet
8. Click on Finish

The whole dataset in the excel has been loaded in the table "MOVIE"

Nazarius

Answer 8

I know I'm a bit late to the game, but I was wanting to do the exact same thing you guys mentioned in your example. I was trying to insert a new list of default rows into a table/list using Access because I've had a lot of SQL experience, I was trying to do it the same way, however as you posters have noted, it's not possible to do the Unions and such.

However I just wanted to post a reply up here because in the case where you're manually typing in the values (string default values in this case) you can simply open Access in datasheet view, copy your data from Excel and just paste it into your Access table (or in my case, SharePoint list). You'll need to make sure you're columns are lined up exactly, but if you were going to manually type in your "insert" sql statements, just putting that info into an Excel spreadsheet shouldn't be a big deal.

In my case, my table/list only had a single column as a lookup, so I just copied the column from notepad++ and pasted it into the datasheet view.

Good luck everyone!

Walt

Answer 9

Check following,

INSERT INTO [Customer] ([Id],[FirstName],[LastName],[City],[Country],[Phone])VALUES(1,'Maria','Anders','Berlin','Germany','030-0074321')
INSERT INTO [Customer] ([Id],[FirstName],[LastName],[City],[Country],[Phone])VALUES(2,'Ana','Trujillo','México D.F.','Mexico','(5) 555-4729')

Reface