Thứ Tư, Tháng Hai 12, 2025
spot_img
HomeINSERT command in SQL - QuanTriMang.com

INSERT command in SQL – QuanTriMang.com

INSERT statement in SQL not too difficult to use. The article will provide you with detailed information about insert into command in SQL.

In previous SQL lessons, you have learned about commands related to databases and data tables in SQL, such as table creation commands and table deletion commands. Starting from this article, we will go into SQL queries, starting with the INSERT INTO query command.

The INSERT INTO command in SQL is used to add a new row of data to a table in the database.

Syntax of the INSERT INTO command:

The INSERT INTO command has 2 basic syntaxes, we will learn both syntaxes in this article.

INSERT INTO command, syntax 1: Specify both the column name and the value to be inserted

INSERT INTO TEN_BANG (cot1, cot2, cot3,...cotN)
VALUES (gia_tri1, gia_tri2, gia_tri3,...gia_triN);

Here, cot1, cot2, cot3,… cotN are the names of the columns in the table where you want to insert more data. value1, value2, value3,…value_valueN are the values ​​to add corresponding to the columns.

INSERT INTO command, syntax 2: If you want to add values ​​to all columns in the table, you can use the INSERT INTO command hereafter:

INSERT INTO TEN_BANG
VALUES (gia_tri1, gia_tri2, gia_tri3,...gia_triN);

Do you notice the difference? In syntax 2, you do not need to list the column names in the table, just make sure that the order of the values ​​you want to add matches the order of the columns in the table.

Example of INSERT INTO command:

The command below will create 6 records for the NHANVIEN table

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (1, 'Thanh', 24, 'Haiphong', 2000.00 ); 

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (2, 'Loan', 26, 'Hanoi', 1500.00 ); 

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (3, 'Nga', 24, 'Hanam', 2000.00 ); 

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (4, 'Manh', 29, 'Hue', 6500.00 ); 

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (5, 'Huy', 28, 'Hatinh', 8500.00 ); 

INSERT INTO NHANVIEN (ID,TEN,TUOI,DIACHI,LUONG) 
VALUES (6, 'Cao', 23, 'HCM', 4500.00 );

You can use syntax 2 to add a 7th record to the NHANVIEN table as follows:

INSERT INTO NHANVIEN
VALUES (7, 'LAM', 29, 'Hanoi', 15000.00 );

After executing all the above commands, we will have the NHANVIEN table as follows:

+----+----------+-----+-----------+---------+ 
| ID |   TEN    | TUOI|  DIACHI   |  LUONG  |
+----+----------+-----+-----------+---------+
| 1  |  Thanh   | 24  | Haiphong  | 2000.00 |
| 2  |  Loan    | 26  | Hanoi     | 1500.00 |
| 3  |  Nga     | 24  | Hanam     | 2000.00 |
| 4  |  Mạnh    | 29  | Hue       | 6500.00 |
| 5  |  Huy     | 28  | Hatinh    | 8500.00 |
| 6  |  Cao     | 23  | HCM       | 4500.00 |
| 7  |  Lam     | 29  | Hanoi     | 15000.00|
+----+----------+-----+-----------+---------+

Add data from another data table

In SQL, you can add data from table B to table A by using the SELECT statement on table B, specifically as follows:

INSERT INTO TEN_BANG_A [(cot1, cot2, ... cot)]
SELECT cot1, cot2, ...cotN
FROM TEN_BANG_B
[WHERE DIEU_KIEN];

In the command above, you will add columns from table B to table A, the parts in brackets [] is possible or not. The SELECT command instead of listing columns, if all columns match, you can use * to replace the column list.

For example: You have 2 data tables with the same structure: NHANVIEN (already has data as in the example above) and NHANVIEN2 (no data yet). You need to fill in data for the NHANVIEN2 table similar to the NHANVIEN table but only select people older than 25 years old.

The syntax will be as follows:

INSERT INTO NHANVIEN2
   SELECT *
   FROM NHANVIEN
   WHERE TUOI > 25;

And the result after inserting data we will have NHANVIEN2 table as follows:

+----+----------+-----+-----------+---------+ 
| ID |   TEN    | TUOI|  DIACHI   |  LUONG  |
+----+----------+-----+-----------+---------+
| 2  |  Loan    | 26  | Hanoi     | 1500.00 |
| 4  |  Mạnh    | 29  | Hue       | 6500.00 |
| 5  |  Huy     | 28  | Hatinh    | 8500.00 |
| 7  |  Lam     | 29  | Hanoi     | 15000.00|
+----+----------+-----+-----------+---------+

Create a data table from an existing data table

In addition to the above two ways to insert data, you can create a data table with the same structure as the original table + with pre-inserted data according to a certain condition.

For example: Create and add a data table NHANVIEN3 with the same structure as the NHANVIEN table and list employees whose age is younger than 25. The command will be as follows:

CREATE TABLE NHANVIEN3 AS
SELECT *
FROM NHANVIEN
WHERE TUOI 

And as a result, you will create table NHANVIEN2 with the content:

+----+----------+-----+-----------+---------+ 
| ID |   TEN    | TUOI|  DIACHI   |  LUONG  |
+----+----------+-----+-----------+---------+
| 1  |  Thanh   | 24  | Haiphong  | 2000.00 |
| 3  |  Nga     | 24  | Hanam     | 2000.00 |
| 6  |  Cao     | 23  | HCM       | 4500.00 |
+----+----------+-----+-----------+---------+

Frequently asked questions when using the INSERT statement in SQL

Question: When setting up a database with clients, you must use the SQL INSERT command to insert information into the database, but how can you be sure not to re-enter the same client information?

Reply: You can ensure that duplicate information is not inserted by using the SQL EXISTS condition.

For example, if you have a table named clients with the key element client_id, you can use the following SQL INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id AS client_id, supplier_name AS client_name, 'advertising' AS client_type
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

The SQL INSERT command inserts multiple records with a subselect. If you want to insert a record, you can use the SQL INSERT command:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

How to use the table dual. dual allows you to enter values ​​in a SELECT statement, even if those values ​​are not currently stored in the table.

Practice using the Insert SQL command

After learning new knowledge, the best way to remember it for a long time is to practice. The exercises below will help you test your knowledge of the Insert SQL statement. You also receive suggested answers after each answer.

Question:

Based on the employees table, insert an employee record with employee_number of 1005, employee_name of Sally Johnson, salary of $58,000, and department_id of 500.

CREATE TABLE employees
( employee_number int NOT NULL,
  last_name char(50) NOT NULL,
  first_name char(50) NOT NULL,
  salary int,
  dept_id int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees
(employee_number, last_name, first_name, salary, dept_id)
VALUES
(1001, 'Smith', 'John', 62000, 500);

INSERT INTO employees
(employee_number, last_name, first_name, salary, dept_id)
VALUES
(1002, 'Anderson', 'Jane', 57500, 500);

INSERT INTO employees
(employee_number, last_name, first_name, salary, dept_id)
VALUES
(1003, 'Everest', 'Brad', 71000, 501);

INSERT INTO employees
(employee_number, last_name, first_name, salary, dept_id)
VALUES
(1004, 'Horvath', 'Jack', 42000, 501);

Suggested answer:

The following INSERT statement will insert this record into the employees table:

INSERT INTO employees 
(employee_number, last_name, first_name, salary, dept_id)
VALUES
(1005, 'Johnson', 'Sally', 58000, 500);

The employee table will now look like this:

Practice exercises using Insert in SQL

Question:

Table-based suppliers With the data provided below, insert a yes supplier record supplier_id is 1000 and supplier_name is Apple:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  city char(50),
  state char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(100, 'Microsoft', 'Redmond', 'Washington');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(200, 'Google', 'Mountain View', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(300, 'Oracle', 'Redwood City', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(400, 'Kimberly-Clark', 'Irving', 'Texas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(500, 'Tyson Foods', 'Springdale', 'Arkansas');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(600, 'SC Johnson', 'Racine', 'Wisconsin');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(700, 'Dole Food Company', 'Westlake Village', 'California');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(800, 'Flowers Foods', 'Thomasville', 'Georgia');

INSERT INTO suppliers
(supplier_id, supplier_name, city, state)
VALUES
(900, 'Electronic Arts', 'Redwood City', 'California');

Suggested answer:

The following SQL INSERT command will insert this record into the suppliers table:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Apple');

The resulting supplier table will look like this:

Example of Insert command in SQL

So after this article, we can create quite a few data tables with available records. Please try practicing more and continue following the next series of articles!

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments