MySQL Insert Data

In the previous article, you have learnt how to create a table and define column names and in this article, you will learn to insert data in a table.

The INSERT keyword is used to insert values in an existing table.

Syntax of MySQL insert query

INSERT INTO Tablename (field1, field2, filed3) VALUES ('value1', 'value2', 'value3');        

In insert query, fields name and their values are separated by comma.

Example

The given query insert data in the 'students' table.

mysql->INSERT INTO students(id, first_name, last_name, age, class) 
	VALUES ('', 'John', 'Brown', '10', '4A');    
    
baseurl.'/images/mysqlinserttable.jpg'; ?> alt="MySQL Insert Table">

We have made 'id' field value blank because we have set it AUTO_INCREMENT during table creation.

You can insert multiple values in a single insert query. All the values of each row set should be comma separated, like -

mysql->INSERT INTO student(id, first_name, last_name, age, class) 
     ->VALUES ('', 'John', 'Brown', '10', '5A'),
     ->VALUES ('', 'Ray', 'Bryan', '11', '6B'),;

If the length of values exceed the defined size of fields then the characters will be truncated to the column's maximum length.


MySQL Load Data

We can also populate records in a created table using text file. For this, create a text file and write data to insert. The one record data should be in one line and separated by tabs. You can write \N in place of NULL values or missing values.

Example

Suppose, there is a 'students.txt' text file in the given format.

\N	Sonia	Roy	9	4A
\N	John	Smith	10	5B
\N	Ria	Gaga	11	6C

The given command populates the text file data in students table.

mysql->LOAD DATA LOCAL INFILE 'C:/db/students.txt' INTO TABLE students 
     ->LINES TERMINATED BY '\r\n';
baseurl.'/images/loaddata.jpg'; ?> alt="MySQL Load Data">