CODEWITHSUNDEEP
sqlcratedb
LearnerForLife
LearnerForLife

Reputation: 157

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 ?

Upvotes: 0

Views: 238

Answers (4)

Sebastian Utz
Sebastian Utz

Reputation: 729

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 0

metase
metase

Reputation: 1309

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

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

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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;

Upvotes: 0

Related Questions