使用顺序主键插入查询 [英] insert query with sequential primary key

问题描述

我正在使用MS Access附加查询将库存交易记录附加到我的ERP数据库(MYSQL).
请告知我将如何修改查询以自动将下一个顺序事务ID(主键)插入Inventory_transaction表中，并能够一次附加多个记录.

I am using an MS Access append query to append inventory transactions to my ERP database (MYSQL).
Please advise how I would go about to modify my query to automatically insert the next sequential transaction ID (primary key) into the Inventory_transaction table, with ability to append multiple records at once.

我现有的查询工作正常，但是仅当我仅追加一条记录时.
我通常需要同时附加多个记录.每个记录都需要具有唯一的顺序事务ID(主键).会有多个用户同时使用此应用程序，因此，我需要尽可能少的重复密钥冲突的机会，以防止回滚.我尝试不使用主键进行追加，以查看数据库是否会自动分配事务ID，但是不幸的是，此ERP字段不是自动编号，因此我无法修改表结构...

My existing query works fine, but only when I append just one record.
I usually need to append multiple records simultaneously. Each record needs to have a unique sequential transaction ID (primary key). There would be multiple users using this app simultaneously, so I need minimal chance of duplicate a key violation, to prevent roll backs. I tried appending without using a primary key to see if my database would automatically assign a transaction ID, but unfortunately this this ERP field is not an auto-number and I cant modify the table structure...

以下是2个查询.
目前，该代码仅用于为一条记录生成交易ID.

Below are 2 queries.
This one currently works for generating a transaction ID for just one record.

SELECT Max([SYSADM_INVENTORY_TRANS].[TRANSACTION_ID])+1 AS new_inventory_transaction_ID
FROM SYSADM_INVENTORY_TRANS;

第二个查询是包含第一个查询的追加查询，如果有人可以修改查询，以便用户能够一次附加具有唯一事务ID的多个记录，我将不胜感激.

The 2nd query is the append query that contains the first query and I would much appreciate it if someone can modify the query so the user has ability to append multiple records at once with a unique transaction ID.

INSERT INTO SYSADM_INVENTORY_TRANS ( TRANSACTION_ID, WORKORDER_TYPE,
  WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID,
  OPERATION_SEQ_NO, REQ_PIECE_NO, PART_ID, TYPE, CLASS, QTY, COSTED_QTY,
  TRANSACTION_DATE, WAREHOUSE_ID, LOCATION_ID, USER_ID, POSTING_CANDIDATE,
  ACT_MATERIAL_COST, ACT_LABOR_COST, ACT_BURDEN_COST, ACT_SERVICE_COST,
  CREATE_DATE, ADD_BURDEN, COUNT_SEQUENCE, DESCRIPTION )
SELECT T.new_inventory_transaction_ID, S.WORKORDER_TYPE, D.WORKORDER_BASE_ID,
  D.WORKORDER_LOT_ID, D.WORKORDER_SPLIT_ID, D.WORKORDER_SUB_ID, D.OPERATION_SEQ_NO,
  D.PIECE_NO, D.auto_issue_part_ID, S.TYPE, S.CLASS, D.[total_auto_issue Qty],
  0 AS Expr6, Date() AS Expr1, D.BACKFLUSH_WHS_ID, D.BACKFLUSH_LOC_ID,
  "SYSADM" AS Expr3, S.POSTING_CANDIDATE, S.ACT_MATERIAL_COST, S.ACT_LABOR_COST,
  S.ACT_BURDEN_COST, S.ACT_SERVICE_COST, Date() AS Expr2, S.ADD_BURDEN,
  S.COUNT_SEQUENCE, "ENTERED WITH ACCESS APP" AS Expr5
FROM tbl_static_autoissue_data AS S,
     tbl_dynamic_autoissue_data AS D,
     qry_transaction_ID_generator AS T;

推荐答案

这里有一些注释可以帮助您实现自己的目标，但是使用自动编号可以使生活更轻松，更安全.就像您提到的MS Access一样，这是VBA.

Here are some notes that may help you towards your goal, however life would be a lot easier and a lot safer with autonumbers. This is VBA as you mention MS Access.

Function NextTranNumber(ByRef FirstTran As Long, _
         ByRef LastTran As Long, Optional BlockSize = 1)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = TestCon ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table that holds a transaction seed
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    FirstTran = rs!ASeqNumber + 1
    rs!ASeqNumber = rs!ASeqNumber + BlockSize
    rs.Update

    LastTran = rs!ASeqNumber
    rs.Close
    Set rs = Nothing
End Function

Sub TransactionProcessing()
Dim FirstTran As Long
Dim LastTran As Long
Dim db As Database
Dim sSQL As String
Dim Block As Long
Dim rs As DAO.Recordset

    Set db = CurrentDb

    'Existing temporary table
    sSQL = "DELETE FROM FETempTrans"
    db.Execute sSQL, dbFailOnError
    'The records to be added to the main table
    sSQL = "INSERT INTO FETempTrans ( ID, AText ) SELECT 0 AS ID, AText FROM Table1"
    db.Execute sSQL, dbFailOnError

    Block = db.RecordsAffected

    'Reserve a transaction block based on the temp table count
    NextTranNumber FirstTran, LastTran, Block

    Set rs = db.OpenRecordset("FETempTrans")

    Do While Not rs.EOF
        rs.Edit
        rs!ID = FirstTran
        rs.Update
        FirstTran = FirstTran + 1
        rs.MoveNext
    Loop

    If FirstTran - 1 = LastTran Then
        'compare the temp set to the main table
        'if it passes, update the main table
    Else
        'fail
    End If
End Sub

这篇关于使用顺序主键插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！