Inserting data into a table

Data can be inserted into a table in many ways.;

Syntax:

insert into table_name ( field1, field2,...fieldN )
   values
   ( value1, value2,...valueN );

Example:

Create table:

%%sql

use retail_db;

DROP TABLE IF EXISTS deptt_<<your lab username>>;

create table deptt_<<your lab username>>(
 depname varchar(30) not null default 'New',
 depcity varchar(30) not null default 'New',
 depstreet varchar(30) not null default 'New',
 depopendate datetime not null default CURRENT_TIMESTAMP
);

Insert records example:

%%sql
insert into deptt_<<your lab username>>(depname,depcity, depstreet,depopendate)
values('Accounts','Bengaluru','Daker Street','2008-7-04');

insert into deptt_<<your lab username>>(depname,depcity, depstreet,depopendate)
values('HR','Delhi','Baker Street','2005-12-23');

insert into deptt_<<your lab username>>(depname,depcity, depstreet,depopendate)
values('Finance','Mumbai','Laker Street','2009-9-19');

A value inside 'values' expression can be a SELECT statement as well.

Syntax

%%sql
insert into deptt_<<your lab username>>(depname,depcity, depstreet,depopendate)
VALUES('Finance','Mumbai',(SELECT ... FROM <<some_table>>),NOW());

In above SQL, department street is being selected from some other table and is inserted into department table.

Whole 'values' clause can also be replaced with SELECT from a table which is essentially copying data from a table into another.

Example

%%sql
insert into deptt_<<your lab username>>(depname,depcity, depstreet,depopendate) SELECT depname,depcity, depstreet,depopendate FROM deptt_<<your lab username>>;

Note - Having trouble with the assessment engine? Follow the steps listed here

Previous Index Next

SQL with Jupyter Notebook Tutorial

Inserting data into a table

XP

Loading comments...