How to write cratedb migration scripts ? Migration Scripts not working

Question

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:

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 ?

Perhaps you need a `;` between the statements? – jarlh Jan 24 '19 at 09:05 — jarlh, Jan 24 '19 at 09:05
I did that and still the same error . – LearnerForLife Jan 24 '19 at 09:18 — LearnerForLife, Jan 24 '19 at 09:18
Remove the `,` just before `)`. – jarlh Jan 24 '19 at 09:19 — jarlh, Jan 24 '19 at 09:19

score 2 · Accepted Answer · answered Jan 24 '19 at 12:39

2

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/.

answered Jan 24 '19 at 12:39

Sebastian Utz

719
3
9

Zaynul Abadin Tuhin · Answer 2 · 2019-01-24T09:18:59.890

0

put a semicolon after create table statement

CREATE TABLE IF NOT EXISTS New_Students (
id int PRIMARY KEY,
name varchar(100),
age varchar(100) -- you need to remove this line coma as well 
);

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

edited Jan 24 '19 at 09:18

answered Jan 24 '19 at 09:05

Zaynul Abadin Tuhin

31,407
5
33
63

@RakeshSrinivasa did you use proper data type in times of table creation i just seen you also need some correction in table create sql check now i edited – Zaynul Abadin Tuhin Jan 24 '19 at 09:20

metase · Answer 3 · 2019-01-24T09:49:38.900

0

There's only 1 statement during a single request as you say it works when you separate the queries, so you do need to do two statements.
Even with million of records it's still a single statements so shouldn't matter on efficiency? You're only running 1 more separate script to create a new table the insert will work as expected taking however long it might.

edited Jan 24 '19 at 09:49

answered Jan 24 '19 at 09:38

metase

1,169
2
16
29

score 0 · Answer 4 · answered Jan 24 '19 at 11:58

Why not just create the table directly from the data?

CREATE TABLE New_Students as
    SELECT id, name, age
    FROM students;

Your problem is the interface that only allows you to send on statement at a time. In this case, though, you may not need two statements.

How to write cratedb migration scripts ? Migration Scripts not working

4 Answers4