cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonykiefer
Super User
Super User

Copy muliple rows by id and reinsert as new record

I'm working with a sql table and I would like to copy all records that match a chosen id and paste them back into the table as new records.  For example...If I wanted to loop through this table and copy all records with a foodID = 1, copy those records and paste them back into the table...how can this be accomplished?

tonykiefer_0-1655840904388.png

 

I tried this but it doesn't work...pardon the pseudo code

Patch( Fruit, Defaults( Fruit ), { id: fruit.id, foodID: fruit.foodid, foodName: fruit.foodname})

ForAll(Fruit,Patch(Fruit,Defaults(Fruit),{id,foodID,foodName}))

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
BCLS776
Super User
Super User

No problem, let's pre-generate the filter results:

With({aFilter: Filter(myTable, /* filter args to get the set of records you want to duplicate */)},
ForAll(
    aFilter As aRecord,
    Patch(myTable, Defaults(myTable),
    {
        //create fields here
    })
))
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

tonykiefer
Super User
Super User

Hey thanks...I took your method one further step.  I copied the record from the db into a collection, then filtered those records before inserting back into the db.  Question....assuming I want to copy all records with a id of 1...when I paste them their id should be 2...How do I get that to happen?

View solution in original post

5 REPLIES 5
BCLS776
Super User
Super User

Try this pseudocode outline:

ForAll(
    Filter(myTable, /* filter args to get the set of records you want to duplicate */) As aRecord,
    Patch(myTable, Defaults(myTable),
    {
        //create fields here
    })
)

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Thank you for the reply...when I tried that I get this error.

tonykiefer_0-1655898707534.png

This process need to occur in the same table.

 

Also, If I'm copying the records and pasting them as new records in the same table, how do I create fields as placeholders?

BCLS776
Super User
Super User

No problem, let's pre-generate the filter results:

With({aFilter: Filter(myTable, /* filter args to get the set of records you want to duplicate */)},
ForAll(
    aFilter As aRecord,
    Patch(myTable, Defaults(myTable),
    {
        //create fields here
    })
))
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
tonykiefer
Super User
Super User

Hey thanks...I took your method one further step.  I copied the record from the db into a collection, then filtered those records before inserting back into the db.  Question....assuming I want to copy all records with a id of 1...when I paste them their id should be 2...How do I get that to happen?


@tonykiefer wrote:

Hey thanks...I took your method one further step.  I copied the record from the db into a collection, then filtered those records before inserting back into the db.  Question....assuming I want to copy all records with a id of 1...when I paste them their id should be 2...How do I get that to happen?


Assuming the id is a column you can change (i.e. not system-generated), you can control it with the record you create in the Patch():

    Patch(myTable, Defaults(myTable),
    {
        id: Value(aRecord.id)+1
    })

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,433)