Hi David,

LabKey SQL doesn't have the functionality to create/drop temp tables. Something like that would have to be done directly on the database itself, so it's possible to have these actions done through a custom module. If you're using Javascript, it may be possible to create a localstore of your data to temporarily hold the information within the browser session to then work with it and/or send the information elsewhere doing something like a POST to the API and having that localstore information in JSON format when it gets applied to the API.

Regarding inserting rows from one table/list to another without downloading is possible through things such as using ETLs.

https://www.labkey.org/Documentation/wiki-page.view?name=etlModule

Another possible way to do this is using the API to do a selectRows on the one table/list and have that success response point to the insertRows (or importRows) API call in a for loop.

Regarding deleting rows quickly, you're likely going to find the same level of performance when it comes to deleting in the UI as well, like the 29 seconds for it to delete 3000 rows when you were doing this via the Javascript API. LabKey does more behind the scenes when it comes to deleting data, not just removing the rows, but also checking for any dependencies as well to make sure the delete is as clean as it can be.

Based on your description, have you considered using an ETL for your work? Although we'd have to fully assess what you're working with, but it sounds like you could use the ETL to obtain your source data, have a stored procedure on the database server to identify the duplicates, then proceed to insert the values into the target table as new rows.

Regards,

Jon