Short SQL to duplicate record?

[jhaganjr]

I want to duplicate a record in the same table - all the data except the key field, of course. The append query is easy enough. For example, here I am duplicating record #2 in my test table called zzTblJunk:

INSERT INTO zzTblJunk ( Field1, Field2, Field3 ) SELECT zzTblJunk.Field1, zzTblJunk.Field2, zzTblJunk.Field3
FROM zzTblJunk WHERE (((zzTblJunk.RecID)=2));

However, with real data there are dozens of fields in the table. I'm trying to shortcut itemizing each and every field of the table in the INSERT and SELECT statements.

If the table has no key field or indexes that don't allow duplicates, then this works:

INSERT INTO zzTblJunk SELECT zzTblJunk.* FROM zzTblJunk
WHERE (((zzTblJunk.RecID)=2));

However, that's not realistic. My real data does, of course, include a key field for the record. So the above syntax creates a duplicate value in RecID (the key field) and fails.

Does anybody know some magic syntax to easily duplicate a record into the same table without duplicating the key fields?

Thanks,
Joe

 

[JBinQLD]

G'day fella, i'm sure someone on here will come up with a funky idea which i look forwad to seeing, but the only shortcut i can readily think of would be:

dim strSQLPart as string
strSQLPart="field1, field2, field3"

docmd.runSQL ("INERT inTO zzTblJunk " & strSQLPart & ") SELECT " & strSQLPart " & FROM zzTblJunk WHERE ((zzTblJunk.RecID)=2);"

maybe this helps? JB

 

[jhaganjr]

Yes. That helps. At least it cuts the typing in half. I only I have to type the field list in once instead of twice.

Thanks!

 

[Remou]

You can use the query design grid to do the typing for you, or you can use a "stored procedure", very roughly:

PARAMETERS lngKey Long; 

INSERT INTO tblB ( 
CreatedBy,
CrDate )  

SELECT CreatedBy,
CrDate
FROM tblA
WHERE Key=[lngKey]

    Set qdf = db.QueryDefs("ThatOneAbove")
    qdf.Parameters!lngKey = Me!txtKey
    qdf.ReturnsRecords = False
    
    qdf.Execute dbFailOnError
    
    intResult = qdf.RecordsAffected

 

[jhaganjr]

Thanks Remou. That's over my head just looking at it, but I can play with it to see what happens. Then I'll understand it.