Copying Data From One List To Another

[Roystreet]

Hello, I'm attempting to copy data over from list to another.  

For Instance the source table is "zest"

I'm trying to copy data over to "holdTable"

Essentially I want the 2 lists have the same data from the same ID.  I want Record ID 200 in the new list to have the same data from Record ID 200 in the source list.

I've tried to build a couple small collections before using the code to actually update all of the records in the live lists.  I eventually want to update all records, not just < 100

ClearCollect(zest,
     Filter(oleBOLT_ACTIONS,
             ID < 100));

ClearCollect(holdTable,
      Filter(hold_oleBOLT_ACTIONS_3,
              ID < 100));

When I've copied the data over, it doesn't work correctly because unfortunately, the source list starts with ID 36 since records 35 and under were deleted in the source list.

When I copy the data over, record 36 from the source list is now record ID 1 in the new list.  This happens in other places too, since maybe record 200 was deleted in the source list, record 200 in the new list won't be the same.

I can easily use a ForAll and patch to copy the data over, but I have the problem.  I've also tried using UpdateIf as below, but to no avail.  

Thanks!

[Drrickryp]

@Roystreet 

Is there a reason for having two identical tables?

The primary key of a table is unique, usually sequential and not reused even if the record is deleted.  All databases are designed this way to maintain referential integrety.

[Roystreet]

Yes, I need to move the list to a new SPO location.  This is of a necessity.  I'm not trying to replace ID's, I'm just trying to take records and place them in a new list with the record ID they were with before.  Ultimately just updating the record in the new list.  While I'm doing this, I can try to do it using Access and connect to those 2 lists.  

[Roystreet · Accepted solution]

This is what I essentially did, by starting a new collection to generate a bunch of dummy record's in the new list.  Then go back and ForAll patch where the ID's match.  Simple as that.  I think I made it more complicated than it really needed to be.  Just make the new list build some records and then patch live data over.

This will simply skip all of the records where there isn't a record in the original list anymore.

NOTE: currentActions is the collection pulling from the old list.

NEW ID's:

ForAll(currentActions,
Patch(BOLT_ACTIONS, Defaults(BOLT_ACTIONS),{ShadowID:ID, Title:ShadowID}))

Then I'll actually populate those records with the corresponding ID:

ForAll(currentActions,
Patch(BOLT_ACTIONS, {ID:ID}, {Owner:Owner}, {OwnerName:OwnerName},{OwnerEmail:OwnerEmail},{ACTION_Number:ACTION_Number}))

ShadowID = ID.  Sometimes when working with records it will give me a delegation warning when I'm filtering and using the ID, but if I use ShadowID, it has no problem.  Access can also help do some of this stuff as well.

Anyone have a better method?  Seems to work for me!  So far 🙂