Today, in this guide, you will know how to insert data in the columns of a table. We are going to use a query
INSERT INTO for the purpose of inserting data. With the help of SQL, we are free to store data in one column or in multiple columns at once.
To inject data into columns, we use query
INSERT INTO table_name(col1_name,col2_name,col3_name)VALUES(col1_value,col2_value,col3_value);
I have a created database named
CREATE DATABASE jai_school;
In which I made a table ‘users‘ which contains 3 columns-
- name – Users’ names will go in this column (first column).
- email – We will store email in this column (second column).
- mobile_no – Mobile numbers will be stored in this (last column).
CREATE TABLE users(name VARCHAR(20),email VARCHAR(50),phone_no INT(15));
Insert data in column
Finally, insert data into the table – ‘users‘. And terminate the query with a semicolon.
INSERT INTO users(name,email,phone_no)VALUES("Ram","email@example.com",1789543210);
According to the above lines:
Ram, firstname.lastname@example.org and 6789543210 will go into name, email, and phone_no columns respectively.
Ram is a string so place it in a double or single quote & numbers without it (any quote).
Each value must be separated with a comma.
For your convenience to understand the code, you can write a single query in multiple lines. But you can’t terminate every line of a single-query. Terminate the query where it lasts. An example is given below.
This is also OK. This whole code is of a single query but in more than one line.
INSERT INTO users(name,email,phone_no) VALUES("Ram","email@example.com",1789543210);
According to rule by Mysql, INT data type can only store a max value of 2147483647 and max digits 255. If you cross the limit than this error will show-
#1264 – Out of range value for column ‘phone_no’ at row 1
To view, this error run this query,
INSERT INTO users(name,email,phone_no)VALUES("Ram","firstname.lastname@example.org",9789543210);
9789543210 > 2147483647 So you get error. Read more on official documentation.
What if I want to add value’s (or store data) in only a column then select that particular column and give value to it.
INSERT INTO users(name)VALUES("Shyam");. Then other columns value in this row will be NULL.
Store multiple data in one column in one query
INSERT INTO table(column)VALUES("RAM"),("Shyam");