Search code examples
sqlcratedb

How to write cratedb migration scripts ? Migration Scripts not working

we have a database which has a table called Students with columns id,name,age,school.

Now i want to write a migration script to copy 3 columns (lets assume i have millions of records) from Students table to New_students table .

Below is my sample script which i have written . It is throwing an error

CREATE TABLE IF NOT EXISTS New_Students (
id PRIMARY KEY,
name string,
age string,
)

INSERT INTO New_Students(id,name,age)
SELECT  id,name,age
FROM students;

When executed above in crateDb Admin UI, i get below error

SQLActionException[SQLParseException: line 8:1: mismatched input 'INSERT' expecting <EOF>]

The above statements works when executing individually .

Question:

  1. Why is above multiple statements are not working , but it works when typed individually ? 2.How to do we copy data from table columns to another new table . Let us say i have millions of records , how do i do it more efficiently ?
Solution

  • The SQL console of the CrateDB AdminUI does not support multiple statements.

    You could use the crash CLI tool instead, which does support multiple statements, see https://crate.io/docs/clients/crash/en/latest/.