SELECT INSERT

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

Can I Select and Insert From and Into the Same Table? Such as...

SplitSQL = "INSERT INTO tblCheckDetaisTMP " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID = " &
Forms!frmFXDiscountSelect!TxtCheckID & " " & _
"AND tblCheckDetailsTMP.CDGroupID = " & Forms!frmFXDiscountItem!TxtGroupID &
" " & _
"AND tblCheckDetailsTMP.CDLineID = " & Forms!frmFXDiscountItem!TxtLineID & "
" & _
"AND tblCheckDetailsTMP.CDItemID = " & Forms!frmFXDiscountItem!TxtItemID &
";"
DoCmd.RunSQL (SplitSQL)

Any downside?
Thanks
DS
 
Can I Select and Insert From and Into the Same Table? Such as...

SplitSQL = "INSERT INTO tblCheckDetaisTMP " & _
"SELECT * FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID = " &
Forms!frmFXDiscountSelect!TxtCheckID & " " & _
"AND tblCheckDetailsTMP.CDGroupID = " & Forms!frmFXDiscountItem!TxtGroupID &
" " & _
"AND tblCheckDetailsTMP.CDLineID = " & Forms!frmFXDiscountItem!TxtLineID & "
" & _
"AND tblCheckDetailsTMP.CDItemID = " & Forms!frmFXDiscountItem!TxtItemID &
";"
DoCmd.RunSQL (SplitSQL)

Any downside?
Thanks
DS
Click to expand...

Since you're inserting all fields - the * - this will fail if the table has a
Primary Key. The query as written would select a subset of the records and
create exact duplicates of them, and since the primary key must by definition
be unique, you CAN'T insert an exact duplicate.

If you want to create new records, and your table has an Autonumber primary
key, replace the SELECT * by SELECT fieldname, fieldname, fieldname going
through all the fields in the table except the PK.

If you don't have a pk, you're in trouble - you will have duplicate records
with no way to distinguish which is which or to update one specific member of
a duplicate set.
 
Back
Top