DML - Data Manipulation Language

DML (Data Manipulation Language) là tập hợp các câu lệnh SQL dùng để thêm, cập nhật, xóa, và đọc dữ liệu trong các tables. Các câu lệnh DML chính: INSERT, UPDATE, DELETE, MERGE, SELECT.

1. INSERT

INSERT cơ bản

-- Chỉ định tên columns (khuyến nghị - luôn làm vậy)
INSERT INTO Employees (FirstName, LastName, Email, DepartmentId, Salary, HireDate)
VALUES ('Nguyen', 'Van An', 'nvan.an@company.com', 3, 50000, '2026-01-15');

-- Không chỉ định columns (nguy hiểm - phụ thuộc vào column order)
INSERT INTO Employees
VALUES (NULL, 'Nguyen', 'Van An', 'nvan.an@company.com', NULL, 50000, '2026-01-15', 1);

-- INSERT và lấy lại ID vừa tạo
DECLARE @newId INT;
INSERT INTO Employees (FirstName, LastName, Email, DepartmentId, Salary)
VALUES ('Tran', 'Thi B', 'tthi.b@company.com', 2, 45000);
SET @newId = SCOPE_IDENTITY();
SELECT @newId;  -- Lấy IDENTITY value vừa insert

Multi-row INSERT

-- INSERT nhiều rows cùng lúc (SQL Server 2008+) - hiệu quả hơn nhiều lần INSERT
INSERT INTO Products (ProductName, Price, CategoryId, Stock)
VALUES 
    ('Widget A',  19.99, 1, 100),
    ('Widget B',  29.99, 1, 200),
    ('Gadget X',  49.99, 2, 50),
    ('Gadget Y',  79.99, 2, 75),
    ('Service Z', 99.99, 3, NULL);

-- Tối đa một VALUES clause có thể có 1000 rows

INSERT INTO…SELECT

-- Sao chép dữ liệu từ table khác
INSERT INTO EmployeesArchive (EmployeeId, FullName, Salary, ArchivedAt)
SELECT EmployeeId, FirstName + ' ' + LastName, Salary, SYSDATETIME()
FROM Employees
WHERE IsActive = 0 AND HireDate < '2020-01-01';

-- INSERT từ nhiều tables với JOIN
INSERT INTO OrderSummary (OrderId, CustomerName, TotalAmount, OrderDate)
SELECT 
    o.OrderId,
    c.FirstName + ' ' + c.LastName,
    SUM(od.Quantity * od.UnitPrice),
    o.OrderDate
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
JOIN OrderDetails od ON o.OrderId = od.OrderId
WHERE o.OrderDate >= '2026-01-01'
GROUP BY o.OrderId, c.FirstName, c.LastName, o.OrderDate;

-- INSERT với CTE
WITH NewHires AS (
    SELECT EmployeeId, FirstName, LastName, DepartmentId
    FROM Employees
    WHERE HireDate >= DATEADD(MONTH, -3, GETDATE())
)
INSERT INTO NewHireReport (EmployeeId, FullName, DeptName)
SELECT 
    nh.EmployeeId,
    nh.FirstName + ' ' + nh.LastName,
    d.DepartmentName
FROM NewHires nh
JOIN Departments d ON nh.DepartmentId = d.DepartmentId;

OUTPUT clause với INSERT

-- OUTPUT: capture rows bị ảnh hưởng
-- INSERTED table: rows được INSERT
-- DELETED table: rows bị DELETE

-- Lấy lại tất cả rows vừa INSERT
INSERT INTO Employees (FirstName, LastName, Email, Salary)
OUTPUT 
    INSERTED.EmployeeId,
    INSERTED.FirstName,
    INSERTED.LastName,
    INSERTED.CreatedAt
VALUES 
    ('Alice', 'Smith', 'alice@co.com', 60000),
    ('Bob',   'Jones', 'bob@co.com',   55000);

-- Lưu OUTPUT vào table khác
DECLARE @InsertedRows TABLE (
    EmployeeId INT,
    FullName   NVARCHAR(200),
    InsertedAt DATETIME2
);

INSERT INTO Employees (FirstName, LastName, Email, Salary)
OUTPUT 
    INSERTED.EmployeeId,
    INSERTED.FirstName + ' ' + INSERTED.LastName,
    INSERTED.CreatedAt
INTO @InsertedRows (EmployeeId, FullName, InsertedAt)
VALUES ('Charlie', 'Brown', 'charlie@co.com', 52000);

SELECT * FROM @InsertedRows;

2. UPDATE

UPDATE cơ bản

-- UPDATE với WHERE (LUÔN kiểm tra WHERE trước khi chạy!)
UPDATE Employees
SET Salary = Salary * 1.10,          -- Tăng 10%
    UpdatedAt = SYSDATETIME()
WHERE DepartmentId = 3
  AND IsActive = 1;

-- Update nhiều columns
UPDATE Products
SET 
    Price       = Price * 0.9,        -- Giảm 10%
    IsOnSale    = 1,
    SaleEndDate = DATEADD(DAY, 30, GETDATE()),
    UpdatedAt   = SYSDATETIME()
WHERE CategoryId = 5;

-- Update dùng subquery
UPDATE Orders
SET TotalAmount = (
    SELECT SUM(od.Quantity * od.UnitPrice)
    FROM OrderDetails od
    WHERE od.OrderId = Orders.OrderId
)
WHERE Status = 'Pending';

UPDATE với JOIN

-- UPDATE với JOIN (SQL Server specific syntax)
UPDATE e
SET 
    e.DepartmentName = d.DepartmentName,  -- Bất thường, nhưng ví dụ về UPDATE với JOIN
    e.ManagerId = d.ManagerId
FROM Employees e
INNER JOIN Departments d ON e.DepartmentId = d.DepartmentId
WHERE d.IsActive = 1;

-- UPDATE nhiều tables liên quan
UPDATE od
SET od.UnitPrice = p.CurrentPrice
FROM OrderDetails od
JOIN Products p ON od.ProductId = p.ProductId
JOIN Orders o ON od.OrderId = o.OrderId
WHERE o.Status = 'Draft'    -- Chỉ update orders chưa confirm
  AND od.UnitPrice <> p.CurrentPrice;

-- Ví dụ thực tế: tăng lương theo department target
UPDATE e
SET e.Salary = e.Salary * (1 + d.SalaryIncreaseRate)
FROM Employees e
JOIN Departments d ON e.DepartmentId = d.DepartmentId
WHERE e.IsActive = 1
  AND e.LastReviewDate < DATEADD(YEAR, -1, GETDATE())
  AND d.SalaryIncreaseRate > 0;

UPDATE với CTE

-- UPDATE qua CTE
WITH EmployeesToUpdate AS (
    SELECT 
        e.EmployeeId,
        e.Salary,
        e.Salary * 1.05 AS NewSalary,
        ROW_NUMBER() OVER (PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) AS rn
    FROM Employees e
    WHERE e.IsActive = 1
)
UPDATE EmployeesToUpdate
SET Salary = NewSalary
WHERE rn <= 3;  -- Chỉ tăng lương top 3 người trong mỗi department

-- UPDATE với ranking
WITH RankedEmployees AS (
    SELECT 
        EmployeeId,
        Salary,
        RANK() OVER (ORDER BY PerformanceScore DESC) AS perf_rank
    FROM Employees
    WHERE ReviewYear = 2025
)
UPDATE Employees
SET Salary = Salary * CASE 
    WHEN re.perf_rank <= 10 THEN 1.15  -- Top 10: +15%
    WHEN re.perf_rank <= 30 THEN 1.10  -- Rank 11-30: +10%
    ELSE 1.05                          -- Others: +5%
END
FROM Employees e
JOIN RankedEmployees re ON e.EmployeeId = re.EmployeeId;

OUTPUT clause với UPDATE

-- Capture giá trị trước và sau khi UPDATE
DECLARE @SalaryChanges TABLE (
    EmployeeId  INT,
    OldSalary   DECIMAL(15,2),
    NewSalary   DECIMAL(15,2),
    ChangedAt   DATETIME2
);

UPDATE Employees
SET Salary = Salary * 1.10
OUTPUT 
    INSERTED.EmployeeId,
    DELETED.Salary,       -- Giá trị CŨ (trước khi update)
    INSERTED.Salary,      -- Giá trị MỚI (sau khi update)
    INSERTED.UpdatedAt
INTO @SalaryChanges (EmployeeId, OldSalary, NewSalary, ChangedAt)
WHERE DepartmentId = 3 AND IsActive = 1;

-- Xem kết quả thay đổi
SELECT 
    EmployeeId,
    OldSalary,
    NewSalary,
    NewSalary - OldSalary AS increase_amount,
    ChangedAt
FROM @SalaryChanges;

3. DELETE

DELETE cơ bản

-- DELETE với điều kiện (LUÔN có WHERE!)
DELETE FROM Employees WHERE EmployeeId = 42;

-- DELETE nhiều rows
DELETE FROM AuditLog
WHERE CreatedAt < DATEADD(YEAR, -2, GETDATE())
  AND IsProcessed = 1;

-- DELETE với subquery
DELETE FROM Orders
WHERE OrderId IN (
    SELECT o.OrderId
    FROM Orders o
    LEFT JOIN OrderDetails od ON o.OrderId = od.OrderId
    WHERE od.OrderId IS NULL  -- Orders không có items
      AND o.CreatedAt < DATEADD(DAY, -7, GETDATE())
);

DELETE với JOIN

-- DELETE với FROM...JOIN (SQL Server syntax)
DELETE od
FROM OrderDetails od
JOIN Orders o ON od.OrderId = o.OrderId
WHERE o.Status = 'Cancelled'
  AND o.CancelledAt < DATEADD(MONTH, -6, GETDATE());

-- Soft delete thường được ưa chuộng hơn hard delete
UPDATE Employees
SET 
    IsDeleted  = 1,
    DeletedAt  = SYSDATETIME(),
    DeletedBy  = SUSER_SNAME()
WHERE EmployeeId = 42;

OUTPUT clause với DELETE

-- Capture rows bị xóa trước khi xóa
DECLARE @DeletedOrders TABLE (
    OrderId     INT,
    CustomerId  INT,
    TotalAmount DECIMAL(10,2),
    DeletedAt   DATETIME2 DEFAULT SYSDATETIME()
);

DELETE FROM Orders
OUTPUT 
    DELETED.OrderId,
    DELETED.CustomerId,
    DELETED.TotalAmount,
    SYSDATETIME()
INTO @DeletedOrders (OrderId, CustomerId, TotalAmount, DeletedAt)
WHERE Status = 'Cancelled'
  AND OrderDate < DATEADD(YEAR, -3, GETDATE());

-- Lưu audit trail
INSERT INTO OrdersDeleteAudit
SELECT * FROM @DeletedOrders;

TRUNCATE TABLE vs DELETE

-- DELETE - chậm, log từng row, có thể ROLLBACK trong transaction
BEGIN TRANSACTION;
DELETE FROM TempData WHERE BatchId = 42;
ROLLBACK;  -- Khôi phục được

-- TRUNCATE - nhanh hơn, không kích hoạt triggers
TRUNCATE TABLE TempData;  -- Xóa tất cả, reset IDENTITY

-- TRUNCATE với Foreign Key: phải disable FK trước
ALTER TABLE ChildTable NOCHECK CONSTRAINT FK_Child_Parent;
TRUNCATE TABLE ParentTable;  -- Error nếu FK vẫn active

-- Delete theo batch để tránh lock cả table
DECLARE @batch_size INT = 10000;
DECLARE @deleted_count INT;

REPEAT_DELETE:
    DELETE TOP (@batch_size)
    FROM LargeAuditTable
    WHERE CreatedAt < DATEADD(YEAR, -5, GETDATE());
    
    SET @deleted_count = @@ROWCOUNT;
    
    WAITFOR DELAY '00:00:01';  -- Cho phép các transaction khác tiếp tục
    
IF @deleted_count = @batch_size GOTO REPEAT_DELETE;
-- Lặp đến khi không còn gì để xóa

4. MERGE Statement (Upsert)

MERGE kết hợp INSERT, UPDATE, DELETE trong một statement:

-- Cú pháp MERGE cơ bản
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.Id = source.Id

WHEN MATCHED AND target.SomeColumn <> source.SomeColumn THEN
    UPDATE SET 
        target.SomeColumn = source.SomeColumn,
        target.UpdatedAt = SYSDATETIME()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, SomeColumn, CreatedAt)
    VALUES (source.Id, source.SomeColumn, SYSDATETIME())

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;  -- Xóa rows trong target không có trong source

Ví dụ thực tế - Product sync

-- Đồng bộ sản phẩm từ staging table vào production
MERGE INTO Products AS target
USING StagingProducts AS source
ON target.ProductSku = source.ProductSku

WHEN MATCHED AND (
    target.ProductName <> source.ProductName OR
    target.Price <> source.Price OR
    target.Stock <> source.Stock
) THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price       = source.Price,
        target.Stock       = source.Stock,
        target.UpdatedAt   = SYSDATETIME()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductSku, ProductName, Price, Stock, CreatedAt)
    VALUES (source.ProductSku, source.ProductName, source.Price, source.Stock, SYSDATETIME())

WHEN NOT MATCHED BY SOURCE AND target.IsActive = 1 THEN
    UPDATE SET target.IsActive = 0, target.UpdatedAt = SYSDATETIME()

OUTPUT
    $action AS merge_action,          -- 'INSERT', 'UPDATE', 'DELETE'
    INSERTED.ProductSku,
    DELETED.Price AS old_price,
    INSERTED.Price AS new_price;

Upsert Pattern (Simple)

-- Đơn giản hơn MERGE khi chỉ cần INSERT hoặc UPDATE

-- Option 1: MERGE (an toàn nhất)
MERGE INTO UserPreferences AS target
USING (VALUES (@userId, @key, @value)) AS source(UserId, PrefKey, PrefValue)
ON target.UserId = source.UserId AND target.PrefKey = source.PrefKey
WHEN MATCHED THEN
    UPDATE SET target.PrefValue = source.PrefValue, target.UpdatedAt = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT (UserId, PrefKey, PrefValue, CreatedAt)
    VALUES (source.UserId, source.PrefKey, source.PrefValue, SYSDATETIME());

-- Option 2: IF EXISTS (đơn giản nhưng có race condition)
IF EXISTS (SELECT 1 FROM UserPreferences WHERE UserId = @userId AND PrefKey = @key)
    UPDATE UserPreferences 
    SET PrefValue = @value, UpdatedAt = SYSDATETIME()
    WHERE UserId = @userId AND PrefKey = @key;
ELSE
    INSERT INTO UserPreferences (UserId, PrefKey, PrefValue)
    VALUES (@userId, @key, @value);

5. SELECT INTO

-- Tạo table mới từ kết quả query (không cần CREATE TABLE trước)
SELECT EmployeeId, FirstName + ' ' + LastName AS FullName, Salary, DepartmentId
INTO EmployeesBackup              -- Tạo table mới
FROM Employees
WHERE IsActive = 1;

-- SELECT INTO tạo table với cùng cấu trúc nhưng KHÔNG copy:
-- - Constraints (PK, FK, UNIQUE, CHECK)
-- - Indexes
-- - Triggers

-- Tạo empty table với cùng cấu trúc
SELECT * INTO EmptyEmployees
FROM Employees
WHERE 1 = 0;  -- Không có rows nào thỏa

-- Tạo table trong database khác
SELECT * INTO OtherDB.dbo.EmployeesBackup
FROM Employees;

-- SELECT INTO với tempdb (temporary table)
SELECT EmployeeId, Salary
INTO #TempSalaries  -- Temp table (# prefix)
FROM Employees;

SELECT EmployeeId, AVG(Salary) OVER (PARTITION BY DepartmentId) AS dept_avg
INTO ##GlobalTemp    -- Global temp table (## prefix)
FROM Employees;

6. Bulk Operations

BULK INSERT

-- Import dữ liệu từ file CSV
BULK INSERT Products
FROM 'D:\Data\products.csv'
WITH (
    FORMAT = 'CSV',              -- SQL Server 2017+
    FIELDTERMINATOR = ',',       -- Phân cách cột
    ROWTERMINATOR = '\n',        -- Phân cách dòng
    FIRSTROW = 2,                -- Bỏ qua header row
    MAXERRORS = 10,              -- Cho phép tối đa 10 lỗi
    ERRORFILE = 'D:\Errors\products_errors.txt',
    TABLOCK                      -- Table-level lock, nhanh hơn
);

-- Import với format file
BULK INSERT Products
FROM 'D:\Data\products.dat'
WITH (
    FORMATFILE = 'D:\Data\products.fmt',  -- File mô tả format
    BATCHSIZE = 5000,            -- Commit mỗi 5000 rows
    TABLOCK
);

OPENROWSET

-- Import từ file ad-hoc
INSERT INTO Products (ProductName, Price, CategoryId)
SELECT ProductName, Price, CategoryId
FROM OPENROWSET(
    BULK 'D:\Data\products.csv',
    FORMATFILE = 'D:\Data\products.fmt'
) AS bulk_data;

-- Import từ Excel (cần OLEDB provider)
SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=D:\Data\products.xlsx;HDR=YES',
    'SELECT * FROM [Sheet1$]'
);

Table-Valued Parameters (TVP)

-- Tạo user-defined table type
CREATE TYPE dbo.ProductList AS TABLE (
    ProductId   INT NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    Price       DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (ProductId)
);

-- Stored procedure nhận TVP
CREATE PROCEDURE dbo.BulkInsertProducts
    @Products dbo.ProductList READONLY  -- READONLY là bắt buộc cho TVP parameters
AS
BEGIN
    INSERT INTO Products (ProductId, ProductName, Price)
    SELECT ProductId, ProductName, Price
    FROM @Products;
END;

-- Sử dụng từ T-SQL
DECLARE @myProducts dbo.ProductList;
INSERT INTO @myProducts (ProductId, ProductName, Price)
VALUES (1, 'Widget A', 19.99), (2, 'Widget B', 29.99);

EXEC dbo.BulkInsertProducts @Products = @myProducts;

7. Transactions với DML

-- Transaction đảm bảo ACID properties
BEGIN TRANSACTION;

    -- Tất cả hoặc không có gì
    INSERT INTO Orders (CustomerId, OrderDate, TotalAmount)
    VALUES (42, GETDATE(), 299.99);

    DECLARE @orderId INT = SCOPE_IDENTITY();

    INSERT INTO OrderDetails (OrderId, ProductId, Quantity, UnitPrice)
    VALUES (@orderId, 101, 2, 99.99),
           (@orderId, 202, 1, 100.01);

    -- Cập nhật stock
    UPDATE Products
    SET Stock = Stock - 2
    WHERE ProductId = 101;
    
    UPDATE Products
    SET Stock = Stock - 1
    WHERE ProductId = 202;

    -- Kiểm tra stock không âm
    IF EXISTS (SELECT 1 FROM Products WHERE ProductId IN (101, 202) AND Stock < 0)
    BEGIN
        ROLLBACK TRANSACTION;
        RAISERROR('Insufficient stock', 16, 1);
        RETURN;
    END

COMMIT TRANSACTION;

-- Transaction với error handling
BEGIN TRY
    BEGIN TRANSACTION;

        UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
        UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;
        
        -- Kiểm tra balance
        IF EXISTS (SELECT 1 FROM Accounts WHERE AccountId = 1 AND Balance < 0)
            THROW 50001, 'Insufficient funds.', 1;

    COMMIT TRANSACTION;
    PRINT 'Transfer successful';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

8. Performance Considerations

Set-based vs Row-by-row Operations

-- BAD: Cursor (row-by-row) - rất chậm cho large datasets
DECLARE @EmployeeId INT, @Salary DECIMAL(15,2);

DECLARE emp_cursor CURSOR FOR
    SELECT EmployeeId, Salary FROM Employees WHERE IsActive = 1;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmployeeId, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Employees
    SET Salary = @Salary * 1.05
    WHERE EmployeeId = @EmployeeId;
    
    FETCH NEXT FROM emp_cursor INTO @EmployeeId, @Salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

-- GOOD: Set-based (một lần cho tất cả)
UPDATE Employees
SET Salary = Salary * 1.05
WHERE IsActive = 1;

-- Set-based thường nhanh hơn cursor 10-100x cho large datasets!

Batch Updates cho Large Tables

-- Chia UPDATE/DELETE thành batches để giảm lock pressure
DECLARE @batch_size INT = 5000;
DECLARE @rows_affected INT = 1;

WHILE @rows_affected > 0
BEGIN
    UPDATE TOP (@batch_size) e
    SET e.IsArchived = 1, e.ArchivedAt = SYSDATETIME()
    FROM Employees e
    WHERE e.IsActive = 0 
      AND e.TerminationDate < DATEADD(YEAR, -3, GETDATE())
      AND e.IsArchived = 0;
    
    SET @rows_affected = @@ROWCOUNT;
    
    -- Nhường CPU và lock cho các processes khác
    WAITFOR DELAY '00:00:00.500';  -- 500ms pause
END;

Avoiding Common DML Pitfalls

-- 1. Luôn có WHERE trong UPDATE/DELETE (kiểm tra kỹ!)
-- Test với SELECT trước
SELECT * FROM Employees WHERE DepartmentId = 99;  -- Xem có đúng không
UPDATE Employees SET Salary = 0 WHERE DepartmentId = 99;  -- Sau đó UPDATE

-- 2. Dùng OUTPUT để audit changes quan trọng
-- 3. Test trong transaction, rollback nếu số rows không đúng
BEGIN TRANSACTION;
UPDATE Products SET Price = Price * 0.9 WHERE CategoryId = 5;
SELECT @@ROWCOUNT AS rows_updated;  -- Kiểm tra số rows
-- Nếu đúng thì: COMMIT TRANSACTION;
-- Nếu sai thì: ROLLBACK TRANSACTION;
ROLLBACK TRANSACTION;  -- Nhớ commit hoặc rollback!

-- 4. MERGE có thể có edge cases - test kỹ
-- 5. Avoid implicit conversion trong WHERE clause của DML
-- BAD:
DELETE FROM Orders WHERE OrderId = '1000';  -- Convert string sang int mỗi row
-- GOOD:
DELETE FROM Orders WHERE OrderId = 1000;    -- Type match, dùng index

-- 6. Với INSERT nhiều rows, dùng multi-row VALUES thay vì nhiều INSERT
-- BAD:
INSERT INTO Tags (Name) VALUES ('SQL');
INSERT INTO Tags (Name) VALUES ('Database');
INSERT INTO Tags (Name) VALUES ('Performance');

-- GOOD:
INSERT INTO Tags (Name) VALUES ('SQL'), ('Database'), ('Performance');

INSERT Performance

-- Tắt indexes trước khi BULK INSERT, bật lại sau
ALTER INDEX ALL ON Products DISABLE;

BULK INSERT Products
FROM 'D:\data\products.csv'
WITH (TABLOCK, BATCHSIZE = 10000);

ALTER INDEX ALL ON Products REBUILD;

-- Tắt CHECK và FK constraints để tăng tốc BULK INSERT
ALTER TABLE Products NOCHECK CONSTRAINT ALL;
-- ... BULK INSERT ...
ALTER TABLE Products WITH CHECK CHECK CONSTRAINT ALL;  -- Re-validate sau

-- Sử dụng minimal logging với SIMPLE recovery model
ALTER DATABASE MyDB SET RECOVERY SIMPLE;
-- ... BULK INSERT với TABLOCK ...
ALTER DATABASE MyDB SET RECOVERY FULL;