Tuesday 28 January 2020

Cassandra: INSERT : Insert data into a table

'INSERT INTO' command is used to insert data into a table.

Syntax
<insertStatement> ::= INSERT INTO <tablename>
                      ( ( <name-list> VALUES <value-list> )
                      | ( JSON <string> ))
                      ( IF NOT EXISTS )?
                      ( USING <option> ( AND <option> )* )?

<names-list> ::= '(' <identifier> ( ',' <identifier> )* ')'

<value-list> ::= '(' <term> ( ',' <term> )* ')'

<option> ::= TIMESTAMP <integer>
           | TTL <integer>

Example
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (1, 'Krishna', 'Gurram', 30);
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (2, 'Ram', 'Gurram', 31);
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (3, 'Vijay', 'Ponnam', 45);

Below step-by-step procedure helps you to create and insert some records to the table employee.

Step 1: Create a keyspace cassandratutorial.
CREATE keyspace cassandratutorial WITH REPLICATION = 
{ 
 'class' : 'SimpleStrategy', 
 'replication_factor' : 1 
};


Step 2: Create a table employee in the keyspace 'cassandratutorial'.
CREATE TABLE IF NOT EXISTS cassandratutorial.employee (
  id INT PRIMARY KEY, 
  firstName VARCHAR,
  lastName VARCHAR,
  age int,
) WITH comment = 'Table to store Employee information';

Step 3: Execute below statements to insert 3 records to the table 'employee'.
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (1, 'Krishna', 'Gurram', 30);
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (2, 'Ram', 'Gurram', 31);
INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (3, 'Vijay', 'Ponnam', 45);

Step 4: Run 'SELECT *' query against employee table to get all the information.
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  30 |   Krishna |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  45 |     Vijay |   Ponnam

(3 rows)

Sample CQL console data

cqlsh> CREATE keyspace cassandratutorial WITH REPLICATION = 
   ... { 
   ... 'class' : 'SimpleStrategy', 
   ... 'replication_factor' : 1 
   ... };
cqlsh> 
cqlsh> CREATE TABLE IF NOT EXISTS cassandratutorial.employee (
   ...   id INT PRIMARY KEY, 
   ...   firstName VARCHAR,
   ...   lastName VARCHAR,
   ...   age int,
   ... ) WITH comment = 'Table to store Employee information';
cqlsh> 
cqlsh> INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (1, 'Krishna', 'Gurram', 30);
cqlsh> INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (2, 'Ram', 'Gurram', 31);
cqlsh> INSERT INTO cassandratutorial.employee (id, firstName, lastName, age) VALUES (3, 'Vijay', 'Ponnam', 45);
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  30 |   Krishna |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  45 |     Vijay |   Ponnam

(3 rows)
cqlsh>


Previous                                                    Next                                                    Home

No comments:

Post a Comment