INSERT trong SQL Server

Trong hướng dẫn này, bạn sẽ tìm hiểu cách sử dụng câu lệnh INSERT trong SQL Server để thêm một hoặc nhiều bản ghi mới vào bảng.

Giới thiệu về câu lệnh INSERT trong SQL Server

Để thêm một hoặc nhiều bản ghi vào bảng, bạn sử dụng câu lệnh INSERT. Dưới đây minh họa cú pháp cơ bản nhất của câu lệnh INSERT:

INSERT INTO table_name (column_list)
VALUES (value_list);

Hãy xem xét cú pháp này chi tiết hơn.

Đầu tiên, bạn chỉ định tên của bảng mà bạn muốn chèn dữ liệu. Thông thường, bạn tham chiếu tên bảng theo tên lược đồ, ví dụ: production.products thì production là tên lược đồvà products là tên bảng.

Thứ hai, bạn chỉ định một danh sách các cột mà bạn muốn chèn dữ liệu. Bạn phải đặt danh sách các cột trong cặp dấu ngoặc đơn và phân tách các cột bằng dấu phẩy.

Nếu một cột của bảng không xuất hiện trong danh sách cột, SQL Server phải có khả năng cung cấp một giá trị để chèn, nếu không thì bản ghi sẽ không được chèn vào bảng.

SQL Server tự động sử dụng giá trị sau cho cột có sẵn trong bảng nhưng không xuất hiện trong danh sách cột của câu lệnh INSERT:

• Giá trị tự tăng tiếp theo nếu cột có thuộc tính IDENTITY.
• Giá trị mặc định nếu cột có giá trị mặc định được chỉ định.
• Giá trị timestamp hiện tại nếu kiểu dữ liệu của cột là kiểu dữ liệu timestamp.
• Giá trị NULL nếu cột là NULL.
• Giá trị tính toán nếu cột là cột được tính toán.

Thứ ba, bạn cung cấp một danh sách các giá trị các cột sẽ được chèn vào mệnh đề VALUES. Mỗi cột trong danh sách cột phải có một giá trị tương ứng trong danh sách giá trị. Ngoài ra, bạn phải đặt danh sách giá trị trong cặp dấu ngoặc đơn.

Ví dụ câu lệnh INSERT trong SQL Server

Hãy tạo một bảng mới có tên là promotions để minh họa cho câu lệnh INSERT trong SQL Server:

CREATE TABLE sales.promotions (
    promotion_id INT PRIMARY KEY IDENTITY (1, 1),
    promotion_name VARCHAR (255) NOT NULL,
    discount NUMERIC (3, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
); 

Trong câu lệnh này, chúng tôi đã tạo một bảng mới có tên là promotions trong lược đồ sales. Bảng promotions có năm cột bao gồm cột id, tên, giảm giá, ngày bắt đầu và ngày hết hạn.

Cột id là một cột định danh (identity) tự tăng, giá trị của nó được SQL Server tự động điền khi bạn thêm một bản ghi mới vào bảng.

Ví dụ câu lệnh INSERT cơ bản trong SQL Server

Câu lệnh sau đây chèn một bản ghi mới vào bảng promotions:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
(
    '2018 Summer Promotion',
    0.15,
    '20180601',
    '20180901'
);

Trong ví dụ này, chúng tôi đã chỉ định các giá trị cho bốn cột trong bảng promotions. Chúng tôi không chỉ định giá trị cho cột promotion_id vì SQL Server tự động cung cấp giá trị cho cột này.

Nếu câu lệnh INSERT thực thi thành công, bạn sẽ nhận được thông báo số lượng bản ghi được chèn vào. Trong trường hợp này, SQL Server đã đưa ra thông báo sau:

(1 row affected)

Để xác minh thao tác chèn, bạn sử dụng truy vấn sau:

SELECT
    *
FROM
    sales.promotions;

Đây là kết quả như bạn mong đợi.

Ví dụ câu lệnh INSERT trả về giá trị được chèn

Để lấy các giá trị được chèn, bạn sử dụng mệnh đề OUTPUT. Ví dụ: câu lệnh sau chèn một bản ghi mới vào bảng promotions và trả về giá trị được chèn của cột promotion_id:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id
VALUES
(
    '2018 Fall Promotion',
    0.15,
    '20181001',
    '20181101'
);

Ngoài sử dụng mệnh để OUTPUT để trả về giá trị cho cột định danh, bạn cũng có thể sử dụng một trong hai câu lệnh dưới đây ngay sau câu lệnh INSERT để đặt được điều tương tự:

-- insert statement here

SELECT @@IDENTITY as LastID
-- or
SELECT SCOPE_IDENTITY() as LastID

Để lấy các giá trị được chèn từ nhiều cột, bạn chỉ định các cột trong đầu ra như trong câu lệnh sau:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id,
 inserted.promotion_name,
 inserted.discount,
 inserted.start_date,
 inserted.expired_date
VALUES
(
    '2018 Winter Promotion',
    0.2,
    '20181201',
    '20190101'
);

Sau đây là đầu ra:

Chỉ định giá trị cho cột định danh trong câu lệnh INSERT

Thông thường, bạn không cần chỉ định giá trị cho cột định danh vì SQL Server sẽ tự động cung cấp giá trị.

Tuy nhiên, trong một số trường hợp, bạn có thể muốn chèn một giá trị vào cột nhận dạng, chẳng hạn như di chuyển dữ liệu.

Xem câu lệnh INSERT sau :

INSERT INTO sales.promotions (
    promotion_id,
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id
VALUES
(
    4,
    '2019 Spring Promotion',
    0.25,
    '20190201',
    '20190301'
);

SQL Server sẽ thông báo lỗi sau:

Cannot insert explicit value for identity column in table 'promotions' when IDENTITY_INSERT is set to OFF.

Để chỉ định giá trị cho cột định danh khi INSERT, trước tiên bạn phải thực hiện câu lệnh sau:

SET IDENTITY_INSERT table_name ON;

Để vô hiệu hóa chỉ định giá trị cho cột định danh khi INSERT bạn sử dụng câu lệnh tương tự với giá trị OFF:

SET IDENTITY_INSERT table_name OFF;

Hãy thực hiện các câu lệnh sau để chèn một giá trị cho cột định danh trong bảng promotions:

SET IDENTITY_INSERT sales.promotions ON;

INSERT INTO sales.promotions (
    promotion_id,
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
(
    4,
    '2019 Spring Promotion',
    0.25,
    '20190201',
    '20190301'
);


SET IDENTITY_INSERT sales.promotions OFF;

Trong ví dụ này, đầu tiên, chúng tôi đã bật chức năng chèn giá trị cho cột định danh, sau đó chèn một bản ghi có giá trị cho cột định danh và cuối cùng đã tắt chức năng chèn giá trị cho cột định danh.

Câu lệnh sau đây cho thấy dữ liệu của bảng promotions sau khi chèn:

SELECT
    *
FROM
    sales.promotions;

INSERT nhiều bản ghi trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách chèn nhiều bản ghi vào một bảng bằng một câu lệnh INSERT duy nhất trong SQL Server.

Để thêm nhiều bản ghi vào một bảng cùng một lúc, bạn sử dụng cú pháp sau của câu lệnh INSERT:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Trong cú pháp này, thay vì sử dụng một danh sách các giá trị, bạn sử dụng nhiều danh sách giá trị được phân tách bằng dấu phẩy để chèn.

Số lượng bản ghi mà bạn có thể chèn cùng một lúc là 1.000 bản ghi bằng cách sử dụng cú pháp câu lệnh INSERT này. Nếu bạn muốn chèn nhiều bản ghi hơn thế, bạn nên xem xét sử dụng nhiều câu lệnh INSERT, câu lệnh BULK INSERT hoặc bảng dẫn xuất.

Lưu ý: cú pháp INSERTnhiều bản ghi này chỉ được hỗ trợ từ SQL Server 2008 trở lên.

Ví dụ INSERT nhiều bản ghi trong SQL Server

Chúng tôi sẽ sử dụng bảng sales.promotions được tạo trong phần đầu của hướng dẫn này để minh họa.

Nếu bạn chưa tạo bảng sales.promotions, bạn có thể sử dụng câu lệnh CREATE TABLE sau:

CREATE TABLE sales.promotions (
    promotion_id INT PRIMARY KEY IDENTITY (1, 1),
    promotion_name VARCHAR (255) NOT NULL,
    discount NUMERIC (3, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
); 

Ví dụ INSERT nhiều bản ghi

Câu lệnh sau chèn nhiều bản ghi vào bảng sales.promotions:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
(
    '2019 Summer Promotion',
    0.15,
    '20190601',
    '20190901'
),
(
    '2019 Fall Promotion',
    0.20,
    '20191001',
    '20191101'
),
(
    '2019 Winter Promotion',
    0.25,
    '20191201',
    '20200101'
);

SQL Server đưa ra thông báo sau cho biết ba bản ghi đã được chèn thành công.

(3 rows affected)

Hãy xác minh chèn bằng cách thực hiện truy vấn sau:

SELECT
    *
FROM
    sales.promotions;

Đây là đầu ra:

Ví dụ INSERT nhiều bản ghi và trả về danh sách id đã chèn

Ví dụ này chèn ba bản ghi vào bảng sales.promotions và trả về danh sách id:

INSERT INTO 
    sales.promotions ( 
        promotion_name, discount, start_date, expired_date
    )
OUTPUT inserted.promotion_id
VALUES
    ('2020 Summer Promotion',0.25,'20200601','20200901'),
    ('2020 Fall Promotion',0.10,'20201001','20201101'),
    ('2020 Winter Promotion', 0.25,'20201201','20210101');

Trong ví dụ này, chúng tôi đã thêm mệnh đề OUTPUT với cột mà chúng tôi muốn trả về bằng cú pháp inserted.column_name. Nếu bạn muốn trả về các giá trị từ nhiều cột, bạn có thể sử dụng cú pháp sau:

OUTPUT inserted.column1, inserted.column2...

INSERT INTO SELECT trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh INSERT INTO SELECT trong SQL Server để thêm dữ liệu từ các bảng khác vào một bảng.

Giới thiệu về câu lệnh INSERT INTO SELECT trong SQL Server

Để chèn dữ liệu từ các bảng khác vào một bảng, bạn sử dụng câu lệnh INSERT INTO SELECT trong SQL Server như sau:

INSERT  [ TOP ( expression ) [ PERCENT ] ] 
INTO target_table (column_list)
query

Trong cú pháp này, câu lệnh chèn các bản ghi được trả về từ truy vấn query vào bảng target_table.

query là bất kỳ câu lệnh SELECT hợp lệ nào lấy dữ liệu từ các bảng khác. Nó phải trả về các giá trị tương ứng với các cột được chỉ định trong column_list.

Mệnh đề TOP là tùy chọn. Nó cho phép bạn chỉ định số lượng bản ghi được trả về bởi truy vấn sẽ được chèn vào bảng đích.

Nếu bạn sử dụng tùy chọn PERCENT, câu lệnh sẽ chèn phần trăm số bản ghi. Lưu ý rằng cách thực hành tốt nhất là luôn sử dụng mệnh đề TOP với mệnh đề ORDER BY.

Ví dụ về câu lệnh INSERT INTO SELECT trong SQL Server

Hãy tạo một bảng có tên sales.addresses để minh họa cho câu lệnh INSERT INTO SELECT trong SQL Server:

CREATE TABLE sales.addresses (
    address_id INT IDENTITY PRIMARY KEY,
    street VARCHAR (255) NOT NULL,
    city VARCHAR (50),
    state VARCHAR (25),
    zip_code VARCHAR (5)
);

Chèn tất cả các bản ghi từ bảng khác

Câu lệnh sau đây chèn tất cả các địa chỉ từ bảng customers vào bảng addresses:

INSERT INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name; 

Để xác minh bạn sử dụng truy vấn sau:

SELECT
    *
FROM
    sales.addresses;

Đây là kết quả:

Chèn một số bản ghi theo điều kiện từ bảng khác

Đôi khi, bạn chỉ cần chèn một số bản ghi theo một điều kiện nào đó từ một bảng khác vào một bảng. Trong trường hợp này, bạn giới hạn số lượng bản ghi được trả về từ truy vấn bằng cách sử dụng các điều kiện trong mệnh đề WHERE.

Câu lệnh sau đây thêm địa chỉ của các cửa hàng ở thành phố  Santa Cruz và Baldwin vào bảng addresses:

INSERT INTO 
    sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.stores
WHERE
    city IN ('Santa Cruz', 'Baldwin')

SQL Server trả về thông báo sau cho biết hai bản ghi đã được chèn thành công.

(2 rows affected)

Chèn N bản ghi đầu tiên từ bảng khác

Đầu tiên, bạn sử dụng câu lệnh sau để xóa tất cả các bản ghi khỏi bảng addresses:

TRUNCATE TABLE sales.addresses;

Thứ hai, để chèn 10 khách hàng hàng đầu tiên được sắp xếp theo tên và họ của họ, bạn sử dụng câu lệnh INSERT TOP INTO SELECT như sau:

INSERT TOP (10) 
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

SQL Server trả về thông báo sau cho thấy mười bản ghi đã được chèn thành công.

(10 rows affected)

Chèn phần trăm bản ghi đầu tiên từ bảng khác

Thay vì sử dụng chính xác số lượng bản ghi, bạn có thể chèn một số lượng bản ghi theo phần trăm tổng số bản ghi.

Đầu tiên, xóa  tất cả các bản ghi từ bảng addresses:

TRUNCATE TABLE sales.addresses;

Sau đó chèn 10 phần trăm bản ghi đầu tiên từ bảng customers được sắp xếp theo tên và họ vào bảng addresses:

INSERT TOP (10) PERCENT  
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

SQL Server đã đưa ra thông báo sau cho biết 145 bản ghi đã được chèn thành công.

(145 rows affected)

Trong hướng dẫn này, bạn đã học cách sử dụng câu lệnh INSERT trong SQL Server để thêm một hoặc nhiều bản ghi mới vào bảng.