Is there an easy way to do the equivalent of a SELECT INTO Table02 from Table01 in GridDB?

Since you cannot rename tables I was wondering if there was a straightforward command you can run in gs_sh to move the data from one table to a new one with a different name and then drop the old table.

I was looking for something similar to the query SELECT * INTO Table02 FROM Table01 you can run in SQL Server, does something like that exist?

I tried running the exact query in gs_sh as an SQL command but it gave me an error because of the INTO command.

gs[public]> select * into Table02 from Table01;
D20332: An unexpected error occurred while executing a SQL. : msg=[[240001:SQL_COMPILE_SYNTAX_ERROR] Parse SQL failed, reason = Syntax error at or near “into” (line=1, column=9) on executing query (sql=“select * into Table02 from Table01”) (db=‘public’) (user=‘admin’) (appName=‘gs_sh’) (clientId=‘fda9d744-d64c-4242-9925-575c45e26eb9:1’) (source={clientId=3, address=}) (address=, partitionId=7386)]

After looking around, I found out there’s not a direct command that can be run in gs_sh or SQL to do it automatically, however, I was able to find a workaround:

  1. You can download the import/export tool from here: GitHub - griddb/expimp: GridDB Export/Import tools
  2. After installing it and changing the configuration file bin/ to your GridDB configuration details, you can run ./gs_export -u admin/admin -c table01 -v, which will create the files public.table01.csv and public.col01_properties.json.
  3. Then you can change the container name in the file public.table01_properties.json created by the process to table02 for example(there is no need to change the file name).
  4. After that, in the csv file public.table01.csv you change the line "$","public.table01" to "$","public.table02".
  5. In gs_export.json you also need to change the container name to the same name you’ve been setting in the other files, table02 in this example.
  6. Following that you can run the command ./gs_import --container table02 -u admin/admin --replace and it should create the container and import the data(alternatively if you have already created the container it should replace it)
  7. Finally, you can just run dropcontainer table01 on gs_sh after successfully migrating the data to the new container.