Insert Query

some colums can have empty values as well, empty values are denoted as null. the absence of value, is denoted by NULL keyword and must be passed corrorpoinding to the column name in insert query.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', NULL);
INSERT INTO apple VALUES (2,"Fuji",  'N', '2022-02-2');

another way to provide an empty or NULL values is to not mention column name and column value in insert query. The MySQL will automatically consider it as NULL.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available ) VALUES (1, "Macintosh", 'Y');
INSERT INTO apple (apple_id, apple_name, available ) VALUES (2,"Fuji", 'Y');
INSERT INTO apple (apple_id, apple_name, available ) VALUES (3,"Gala",  'Y');
INSERT INTO apple (apple_id, apple_name, available ) VALUES (4,"Jonagold", 'N');

Insert Multiple Rows in Single Query

if multiple rows need to be inserted into table, then writing insert query for each row is redundant, MySql provides an easy way to mention column names only once and various values.

The column name is specified only once, all the values must have the same position corresponding to the column name.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES
(1,"Macintosh", 'Y', '2022-01-1'),
(2,"Fuji",  'N', '2022-02-2'),
(3,"Gala",  'Y', '2022-03-3'),
(4,"Jonagold", 'N', '2022-04-4'),
(5,"GrannySmith", 'Y', '2022-05-5');

Inssert Data from Another Query Result

Some times a table must be populated with values already present in another table. MySql allows to insert into a table based on results of Select Query from other tables.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1'),
(2,"Fuji",  'N', '2022-02-2'),
(3,"Gala",  'Y', '2022-03-3'),
(4,"Jonagold", 'N', '2022-04-4'),
(5,"GrannySmith", 'Y', '2022-05-5');

CREATE TABLE apple_temp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) select apple_id, apple_name, available, available_date  from apple;

If required to fill new table with filtered results only, the Select query allows all valid conditions and operators supported by MySql.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple VALUES (1,"Macintosh", 'Y', '2022-01-1'),
(2,"Fuji",  'N', '2022-02-2'),
(3,"Gala",  'Y', '2022-03-3'),
(4,"Jonagold", 'N', '2022-04-4'),
(5,"GrannySmith", 'Y', '2022-05-5');

CREATE TABLE apple_temp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) select apple_id, apple_name, available, available_date  from apple where available ='Y';

Insert Record Only If Not Exist

Suppose a new table needs to be populated with data from old table, but some records of old table is already present in new table, it will result in a duplicity of values as shown below.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji",  'N', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala",  'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');

CREATE TABLE apple_temp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (6,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (7,"Fuji",  'N', '2022-02-2');

INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) SELECT apple_id, apple_name, available, available_date FROM apple;

Define Duplicasy Critera

In order to make sure the new table doesn't have duplicates first need to provide the definition of duplicates. Both table may have certain columns based on which a row can be labelled as duplicated. Where clause in select query will allows us to provide the filtering query to remove duplicity.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji",  'N', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala",  'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');

CREATE TABLE apple_temp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (6,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (7,"Fuji",  'N', '2022-02-2');

INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) SELECT apple_id, apple_name, available, available_date FROM apple WHERE NOT EXISTS (SELECT * from apple_temp WHERE apple.apple_name=apple_temp.apple_name);

Insert Record If Exist

In addition to If Not Exist, MySql supports If Exist as well, it will insert only those records in new table which already exist in old table.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji",  'N', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala",  'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');

CREATE TABLE apple_temp (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (6,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) VALUES (7,"Fuji",  'N', '2022-02-2');

INSERT INTO apple_temp (apple_id, apple_name, available, available_date ) SELECT apple_id, apple_name, available, available_date FROM apple WHERE EXISTS (SELECT * from apple_temp WHERE apple.apple_name=apple_temp.apple_name);

Insert Date in MySql

Inserting Date into table is the most common problem, since it supports different formats. Date data type can be divided into Date and Datetime ie Date with Timestamp.

Sql Date Insertion

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (1,"Macintosh", '2020-01-1');
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (2,"Fuji", '21-02-02');
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (3,"Gala", STR_TO_DATE('05 MAY 2025', '%d %b %Y'));

Sql Datetime Insertion

USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available_date DATETIME, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (1,"Macintosh", '2020-01-1 01:01:01');
INSERT INTO apple (apple_id, apple_name, available_date ) VALUES (2,"Fuji", '21-02-02 02:02:02');