sql: SELECT INTO <session_var> FROM <table> [WHERE...] [AS OF SYSTEM TIME...]

[HonoreDB]

Is your feature request related to a problem? Please describe.
Moving data from one location to another can be cumbersome if INSERT INTO ... SELECT is not available. In particular, suppose some rows were accidentally deleted from a production table with no recent backup. Those rows are visible via SELECT ... AS OF SYSTEM TIME 'before the delete', but the easiest way of getting them back into the table is BACKUP ... AS OF SYSTEM TIME and then restoring from backup, possibly into a separate table so that the old data can be merged in. Not the easiest thing to do quickly, especially since if you're in this situation, you probably aren't set up for easy backup/restore. If the data is small enough, you can also query with a format like csv, then IMPORT it or copy/paste it into an insert statement, but that requires creating a new client connection and is error-prone. It would be great to have a way of quickly recovering using pure SQL.

Describe the solution you'd like
There is a SQL quasi-standard form that is basically copy/paste: SELECT * INTO old_data FROM users would create a session variable named old_data, give it the type appropriate to an array of rows from users, and populate it with the result of the query, returning the number of rows rather than the data. old_data could then be interacted with like any other array of tuples, so that you could do INSERT INTO new_users VALUES (old_data). We'd want an upper bound on the size of variables created this way, with an error pointing you toward backup or export functionality if that limit is exceeded. Since session variables exist outside of database time, no paradoxes would apply if you used AS OF SYSTEM TIME to populate one and then wrote from it in the present, just like if you'd literally copy/pasted.

Describe alternatives you've considered
Basically anything else that lets you write to the present from a system time in the past, such as unreverting #57320, would solve this, but looks like that's been tricky. Similarly, SELECT INTO table is a Postgres alias to the same idea. Restoring a table in-place to a recent timestamp would likely be ideal but has unclear interactions with in-flight transactions. However, SELECT INTO variable would also just be more convenient in some cases than using subqueries even when they'd work fine.

Jira issue: CRDB-9732

[rafiss]

Here is the PostgreSQL documentation for SELECT INTO https://www.postgresql.org/docs/current/sql-selectinto.html

@HonoreDB It looks like in PG this syntax creates a new table. I don't think we'd want to diverge from the PG semantics.

There is a larger epic with SQL-Queries for supporting AS OF SYSTEM TIME in more places. I wonder if that would help the use case you're talking about. https://cockroachlabs.atlassian.net/browse/CRDB-9838

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!