sql - 检测是否从INSERT INTO操作中排除了某些记录

Question

我要插入的表的2列上有一个复合主键。我来自使用SQL Server的工作，我知道，如果尝试将重复的键值插入PK表，则会引发错误。

我的问题是，我的代码没有引发这种错误。您可以看一下它，看看代码是否有问题？还是Access不会因此类违规而引发错误？

[编辑]

我想我正在寻找一种方法来仅确认尝试插入重复记录。我希望保留当前功能(将重复项扔掉；插入有效记录)。我不希望整个INSERT都回滚。

我的代码如下。

Function InsertData(Ignore As String)  

    ' define file path of CSV to be imported
    Dim CurrentDate As String
    Dim CurrentYear As String

    CurrentDate = Format(Date, "yyyymmdd")
    CurrentYear = Format(Date, "yyyy")


    Dim Exfile As String
    Exfile = iPath + "\" + CurrentYear + "\" + "FileName" + CurrentDate + ".txt"

    'this calls a saved import routine
    DoCmd.RunSavedImportExport "tbl_TEMP"


    'merge data with that already existing in tbl_Perm.
    'the clustered PK on product_ID and As_of_Date prevents dup insertion
    Dim dbs As Database
    Dim errLoop As Error

    Set dbs = OpenDatabase(iPath + "\ExDatabase.mdb")
        dbs.Execute " INSERT INTO tbl_Perm (Col1,Col2,Date_Created) " _
                    & "SELECT ColA + ColB, ColC, Format$(Now(),'Short Date')" _
                    & "FROM tbl_TEMP;"

        ' Trap for errors, checking the Errors collection if necessary.
        On Error GoTo Err_Execute

        'delete temp table
        dbs.Execute "DROP TABLE tbl_TEMP;"

    dbs.Close

Err_Execute:
    ' Notify user of any errors that result from
    ' executing the query.
    If DBEngine.Errors.Count > 0 Then
    For Each errLoop In DBEngine.Errors
    MsgBox "Error number: " & errLoop.Number & vbCr & _
    errLoop.Description
    Next errLoop
    End If
 Resume Next

End Function

Answer 1

如果要允许INSERT继续进行并确定是否有重复项被拒绝，则可以执行以下操作

Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
Dim sqlSelect As String, sourceRecords As Long
Set cdb = CurrentDb
sqlSelect = _
        "SELECT ColA + ColB, ColC, Format$(Now(),'Short Date') " & _
        "FROM tbl_TEMP"
Set rst = cdb.OpenRecordset("SELECT COUNT(*) AS n FROM (" & sqlSelect & ")", dbOpenSnapshot)
sourceRecords = rst!n
rst.Close
Set rst = Nothing
Set qdf = cdb.CreateQueryDef("", _
        "INSERT INTO tbl_Perm (Col1,Col2,Date_Created) " & sqlSelect)
qdf.Execute
If qdf.RecordsAffected < sourceRecords Then
    Debug.Print sourceRecords - qdf.RecordsAffected & " record(s) not inserted"
End If
Set qdf = Nothing
Set cdb = Nothing