MS SQL Server: INSERT Statement in SQL Server

The INSERT statement in SQL Server (Transact-SQL) helps insert one or more records into a table.

INSERT command syntax

Insert a record

In its simplest form, the INSERT command syntax inserts a record with the VALUES keyword

 INSERT INTO  bang 
 (cot1, cot2, …) 
 VALUES 
 (bieuthuc1, bieuthuc2, …), 
 (bieuthuc1, bieuthuc2, …), 
 . ; 

The full syntax of the INSERT command inserts a record using VALUES keyword

 INSERT INT O bang 
 (cot1, cot2, …) 
 VALUES 
 ( DEFAULT | NULL | bieuthuc1, 
  DEFAULT | NULL | bieuthuc2, 
 … 
 ); 

Or the syntax to insert a record using the keyword DEFAULT VALUES

 INSERT INT O bang 
 (cot1, cot2, …) 
 DEFAULT VALU ES; 

Insert multiple records

In the simplest form, the syntax for inserting multiple records is by sub-select

 INSERT I NTO bang 
 (cot1, cot2, …) 
 SELECT bieuthuc1, bieuthuc2, … 
 FROM bang_nguon 
 [WHERE dieuk ien]; 

Full syntax for inserting multiple desks with sub-select

 INSERT [TOP ( tri_dau) [PERCENT] ] 
 INTO bang 
 (cot1, cot2, …) 
 SELECT bieuthuc1, bieuthuc2, … 
 FROM bang_nguon 
 [WHERE dieukien]; 

Variable name or variable value

state

Table to insert records into.

cot1, cot2

Columns in the table to insert values

bieuthuc1, bieuthuc2

Value to specify in the column in the table. cot1 will be assigned the value of bieuthuc1, cot2 will be assigned the value of bieuthuc2.

TOP (giatri_dau)

Option. If specifically, it will insert the first value of the row based on giatri_dau. As an example,, TOP (10) will insert the first 10 rows from the result set.

PERCENT

Option. If specified, the first rows are based on the percentage of giatri_dau of the result set. As an example,, TOP (10) PERCENT will insert 10% of the first value in the result set.

bang_nguon

Source table (original table) to insert data from another table.

WHERE dieukien

Option. Sending conditions are met so that the record is inserted.

Note

• When inserting records into a table with the INSERT statement, you must provide values for NOT NULL columns.
• You can remove columns from the INSERT statement if the column allows NULL values.

As an example, - use the keyword VALUES

 INSERT INT O nhanvien 
 (nhanvien_id, ten, ho) 
 VALUE 
 (10, 'Anderson', 'S arah'); 

This INSERT statement will result in 1 record being inserted into the table. This record must have nhanvien_id of 10, they are Anderson and their name is Sarah.

This syntax can help insert more than 1 record at a time. Eg:

 INSERT INTO anvien 
 (nhanvien_id, ho, ten) 
 VALUES 
 (19, 'Anderson', 'Sarah'), 
 (11, 'Johnson', 'Dale') ; 

As an example,, the INSERT statement above shows that it is possible to insert more than 1 record with the VALUES keyword. In this example, 2 records are inserted into the table. The first record has 10 minutes, they are Anderson and their name is Sarah. The second record has nhanvien_id is 11, they are Johnson and the name is Dale.

The above command is equivalent to the INSERT statements below.

 INSERT INTO anvien 
 (nhanvien_id, ho, ten) 
 VALUES 
 (10, 'Anderson', 'Sa rah'); 

 INSERT INTO anvien 
 (nhanvien_id, ho, ten) 
 VALUES 
 (11, 'Johnson', 'Dal e'); 

As an example, - use the keyword DEFAULT VALUES

 INSERT IN TO nhanvien 
 (nhanvien_id, ho, ten) 
 DEFAULT VA LUES; 

This INSERT statement inserts a record into the table. New records are created by default values for employee ID, family and name information fields.

As an example, - use the SELECT command

It is possible to create a more complex INSERT statement with the SELECT statement as shown below.

 INSERT INTO da nhba 
 (danhba_id, ho, ten) 
 SELECT nhanvien_id, ho, ten 
 FROM nhanvien 
 WHERE nhanvien_id <= 100;