Multiple Insert With Same Column (Ms Access)

Insert multiple rows using one insert statement in Access 2010

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.

Multiple INSERT with same column (MS ACCESS)

Just to be clear for someone who is looking for the answer. Here is the correct query. As @Lee_Mac stated plus a little add:

INSERT INTO Ink ( DesignID, Mesh, Ink )
SELECT * FROM
(
SELECT DesignID, Mesh1 as Mesh, Ink1 as Ink
FROM Design
WHERE Ink1 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh2, Ink2
FROM Design
WHERE Ink2 IS NOT NULL
UNION ALL
SELECT DesignID, Mesh3, Ink3
FROM Design
WHERE Ink3 IS NOT NULL
)

This will work and run with no problems. First SELECT should be selected as names of the fields you are inserting in to in order to run and work.

inserting multiple rows with 1 query

With Access SQL you can't combine two INSERT statements. You could run each of them separately. But if you need to do it with a single statement, you will need to use a more complex query.

INSERT INTO Employee
SELECT '1','b','c'
FROM Dual
UNION ALL
SELECT '2','d','e'
FROM Dual;

Dual is a custom table designed to always contain only one row. You can create your own Dual table using the instructions from this Stack Overflow answer.

However, you don't actually need a custom table for this purpose. Instead of Dual, you can use any table or query which returns only one row.

Access SQL - Insert multiple rows wont work

The link you have given already state that you CANNOT do

insert into foo (c1, c2, c3)
values ("v1a", "v2a", "v3a"),
       ("v1b", "v2b", "v3b"),
       ("v1c", "v2c", "v3c")

Which is exactly the way you are doing it now.

Try

INSERT INTO EMP_1 (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE)
    SELECT *
    FROM (select top 1 "101" AS EMP_NUM, "News" AS EMP_LNAME, "John" AS EMP_FNAME, "G" AS EMP_INITIAL, "08-Nov-00" AS EMP_HIREDATE, "502" AS JOB_CODE from onerow
    union all
    select top 1 "102" AS EMP_NUM, "Senior" AS EMP_LNAME, "David" AS EMP_FNAME, "H" AS EMP_INITIAL, "12-Jul-89" AS EMP_HIREDATE, "501" AS JOB_CODE from onerow)

I am not sure about MS-Access SQLs. But " is differ from ' in SQL. It seems that the link you give uses " as the answer. Why not give it a try? But generally ' should be used for string values.

SQL code to insert multiple rows in ms-access table

I found an elegant solution within R, (the software I'm working with). The RODBC package has a function sqlSave which allow to append and entire data.frame at once to a table. This works almost twice as fast as individual inserts within a transaction.

library(RODBC)
MDB <- odbcConnectAccess("database.mdb")
sqlSave(channel = MDB, dat = sims, tablename = "tblSimulation", append = TRUE, rownames = FALSE)
odbcClose(MDB)

insert multiple rows with one query using ms access db

Most databases are able to do all this work much more efficiently entirely in the database. Certainly in SQL Server I could get entire thing down to a single query. Access is a little different, since vbscript is its procedural language, rather than something more like t-sql. There's still probably a way to do it, but since what you have works, we can at least focus on making that better.

GridViews are visual constructs that will use up extra memory and resources. If Access won't do a real INSERT/SELECT, you can at least read direct from the previous result set into your insert. You can also improve on this significantly by using parameters and re-using a single open connection for all the inserts:

Dim cnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Shantara Production IT.mdb"
Dim SQLDown As String = _
     "SELECT DISTINCT POH.BatchNo, SSCDD.ComponentID
      FROM ([KN - ProductionOrderHeader] AS POH
      INNER Join [FG - End Product Codes] AS EPC
        On POH.ProductID = EPC.ProductID)
      INNER Join([KN - ProductionOrderDetails] AS POD
      INNER Join [FG - Style Size Comp Def Details]  AS SSCDD
           On POD.SizeID = SSCDD.SizeID) 
           On (POH.BatchNo = POD.BatchNo) 
                And (EPC.StyleID = SSCDD.StyleID)
     WHERE POH.BatchNo = ? "
Dim SQLUp As String = _
    " INSERT INTO [KN - ProductionMachineAllocation] 
      (BatchNo, ComponentID) 
       VALUES( ?, ? )"

Dim dt As New DataTable
Using con As New OleDbConnection(cnString), _
      cmd As New OleDbCommand(SQLDown, con)

    'Guessing at parameter type/length here.
    'Use the actual column type and size from your DB
    cmd.Parameters.Add("@BatchNo", OleDbType.VarWChar, 10).Value = BatchNo

    con.Open()
    dt.Load(cmd.ExecuteReader())
End Using

Using con As New OleDbConnection(cnString), _
      cmd As New OleDbCommand(SqlUp, con)

    'Guessing at parameter types/lengths again
    cmd.Parameters.Add("@BatchNo", OleDbType.VarWChar, 10)
    cmd.Parameters.Add("@ComponentID", OleDbType.Integer)

    'Connection is managed *outside of the loop*. Only one object created, only one negotiation with DB
    con.Open()
    For Each row As DataRow In dt.Rows
          cmd.Parameters(0).Value = row(0)
          cmd.Parameters(1).Value = row(1)
          cmd.ExecuteNonQuery()
    Next
End Using

Normally, with any ADO.Net provider you do not re-use your connection or command objects. You want a new connection object for every query sent to the DB to allow connection pooling to work correctly. Using the connection in a tight loop like this for the same query is one of the few exceptions.

I might be able to improve further by sticking with the active DataReader, rather than first loading it into a DataTable. That would allow us to avoid loading the entire result set into memory. You would only ever need one record in memory at a time. Certainly this would work for Sql Server. However, Access was designed mainly as a single-user database. It doesn't really like multiple active connections at once, and I'm not sure how it would respond.

It would also be nice to be able to do all of this work in a transactional way, where there's never any risk of it failing part way through the loop and getting stuck with half the updates. Sql Server would handle this via a single INSERT/SELECT query or with an explicit transaction. But, again, this isn't the kind of the Access is designed for. It probably does have a way to do this, but I'm not familiar with it.

MS Access insert multiple rows

INSERT INTO syt_abrdat (id,begindat,einddat,periode,groep) 
SELECT * FROM 
   (SELECT TOP 1 1 AS id, 9999 AS begindat, 9999 as einddat, '---' as periode, 'WAAR' as groep FROM onerow UNION ALL
    SELECT TOP 1 2 AS id, 9999 AS begindat, 9999 as einddat, 'XXX' as periode, 'WAAR' as groep FROM onerow)

Comments:

• Use single quotes for literal string values
• Do not use quotes for literal numbers
• the table onerow must contain at least 1 record

MS Access: How to add multiple rows to table within 1 query

I think you can do what you need with a fairly simple VBA procedure.

For example, to load 1000 rows into my "Table3" with False stored in the field named "bool_field", I can call the procedure below like this ...

AddRows "Table3", "bool_field", False, 1000

That table's other field is AutoNumber type, so the db engine takes care of its value.

Public Sub AddRows(ByVal pTable As String, _
        ByVal pField As String, _
        ByVal pValue As Variant, _
        ByVal pHowMany As Long)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pTable, , dbAppendOnly)
    With rs
        For i = 1 To pHowMany
            .AddNew
            .Fields(pField).Value = pValue
            .Update
        Next
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

If you prefer a query-based approach, you could execute an INSERT as many times as required within the For i = 1 To pHowMany section.

---

Related Topics