google cloud platform - How to create temporary tables in Spanner - Stack Overflow

admin管理员组

文章数量:1122846

Is there any way to create a temporary table in Google Cloud Spanner through:

SELECT * INTO <temp table> FROM <table name>

where I can then use the temp table with multiple other queries in the same batch?

Is there any way to create a temporary table in Google Cloud Spanner through:

SELECT * INTO <temp table> FROM <table name>

where I can then use the temp table with multiple other queries in the same batch?

• google-cloud-platform
• google-cloud-spanner

Share Improve this question Follow asked yesterday Raj ChaudharyRaj Chaudhary 1,75233 gold badges2222 silver badges3434 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

That is not directly supported in the way that you describe. There are a couple of alternatives:

1. Create a normal table and fill it with the data:

CREATE TABLE temp_table (id int64, value string(max)) primary key (id);
INSERT INTO temp_table (id, value) SELECT * FROM table_name;

Note that the insert statement is limited by the mutation limit in Spanner: https://cloud.google.com/spanner/quotas#limits-for (Mutations per commit)

2. Use a common table expression:

WITH temp_table AS (
  SELECT *
  FROM table_name
)
SELECT col1, col2
FROM temp_table

One limitation of common table expressions, is that they are only visible to the query they are defined. It is not possible to re-use the same common table expression in multiple queries, other than by repeating the definition in the query.

https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#with_clause

本文标签： google cloud platformHow to create temporary tables in SpannerStack Overflow