find duplicate insert into another table

Question

I have a code that that loads my data into sql table. I want know that is there any way to load data into some other table if there is any duplicate value. if yes can you help me. My query is:

"insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNbr") & "', '" & rcdDNE("FirstName") & "', '" & rcdDNE("MiddleName") & "', '" & rcdDNE("LastName") & "', '" & rcdDNE("Amount") & "')"

that will insert data into t_DATA_DneFrc table. Now i want change the query if there is duplicate insert that row into t_DATA_DneFrcDuplicate. Is that possible?

 ' Write records to Database

frmDNELoad.lblStatus.Caption = "Loading data into database......"

Dim lngRecCount As Long
lngRecCount = 0
rcdDNE.MoveFirst

 With cmdCommand
    .ActiveConnection = objConn
    .CommandText = "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNbr") & "', '" & rcdDNE("FirstName") & "', '" & rcdDNE("MiddleName") & "', '" & rcdDNE("LastName") & "', '" & rcdDNE("Amount") & "')"
    .CommandType = adCmdText

End With

Set rcddnefrc = New ADODB.Recordset
With rcddnefrc
    .ActiveConnection = objConn
    .Source = "SELECT * FROM T_DATA_DNEFRC"
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open
End With

Do Until rcdDNE.EOF
    lngRecCount = lngRecCount + 1
    frmDNELoad.lblStatus.Caption = "Adding record " & lngRecCount & " of " & rcdDNE.RecordCount & " to database."
    frmDNELoad.Refresh
    DoEvents
    Call CommitNew
    rcdDNE.MoveNext
Loop

frmDNELoad.lblStatus.Caption = "DNE Processing Complete."
frmDNELoad.Refresh

End Function
Sub CommitNew()

'     Add records to DneFrc table
With rcddnefrc
    .Requery
    .AddNew
    .Fields![RTN] = rcdDNE.Fields![RTN]
    .Fields![AccountNbr] = rcdDNE.Fields![AccountNbr]
    .Fields![FirstName] = rcdDNE.Fields![FirstName]
    .Fields![MiddleName] = rcdDNE.Fields![MiddleName]
    .Fields![LastName] = rcdDNE.Fields![LastName]
    .Fields![Amount] = rcdDNE.Fields![Amount]
    .Update
End With

Answer 1

You'll need to run a second query, which should look like

insert into t_DATA_DneFrcDuplicate 
      (RTN, AccountNbr, FirstName, MiddleName, 
       LastName, Amount, Occurances)
  select RTN, AccountNbr, FirstName, MiddleName, 
         LastName, Amount, COUNT(*)
  from   t_DATA_DneFrc
  group by RTN, AccountNbr, FirstName, MiddleName, 
           LastName, Amount
  having COUNT(*) > 1

(This is Oracle syntax; hopefully the same will work for you.)