SELECT INSERT

[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

 

[John W. Vinson]

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

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.