DML: Inserting, Updating and Deleting Data

6.1 INSERT

-- Single row
INSERT INTO dbo.Customers (FirstName, LastName, Email, City)
VALUES (N'Asha', N'Rao', '[email protected]', N'Pune');

-- Multiple rows (up to 1000)
INSERT INTO dbo.Categories (CategoryName) VALUES (N'Electronics'), (N'Books'), (N'Toys');

-- INSERT ... SELECT
INSERT INTO dbo.CustomerArchive (CustomerID, FullName)
SELECT CustomerID, FirstName + ' ' + LastName FROM dbo.Customers WHERE CreatedAt < '2020-01-01';

-- INSERT ... EXEC
INSERT INTO #Who EXEC sp_who2;

-- Default values
INSERT INTO dbo.AuditLog DEFAULT VALUES;

-- SELECT INTO (creates a new table)
SELECT * INTO dbo.Customers_Backup FROM dbo.Customers;
SELECT * INTO #EmptyCopy FROM dbo.Customers WHERE 1 = 0;   -- structure only

6.2 UPDATE

UPDATE dbo.Products SET UnitPrice = UnitPrice * 1.10 WHERE CategoryID = 1;

-- UPDATE with JOIN
UPDATE p
SET    p.Stock = p.Stock - oi.Quantity
FROM   dbo.Products p
JOIN   dbo.OrderItems oi ON oi.ProductID = p.ProductID
WHERE  oi.OrderID = 1001;

-- UPDATE TOP
UPDATE TOP (100) dbo.Orders SET Status = 'Shipped' WHERE Status = 'Pending';

6.3 DELETE

DELETE FROM dbo.Orders WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

DELETE o FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
WHERE c.Country = N'Testland';

-- Batched delete (avoids log growth and lock escalation)
WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.AuditLog WHERE LogDate < DATEADD(YEAR, -2, GETDATE());
    IF @@ROWCOUNT = 0 BREAK;
END

6.4 The OUTPUT Clause

INSERT INTO dbo.Customers (FirstName, LastName)
OUTPUT inserted.CustomerID, inserted.FirstName
VALUES (N'Ravi', N'Kumar');

UPDATE dbo.Products SET UnitPrice = UnitPrice * 0.9
OUTPUT deleted.ProductID, deleted.UnitPrice AS OldPrice, inserted.UnitPrice AS NewPrice
INTO dbo.PriceHistory (ProductID, OldPrice, NewPrice)
WHERE CategoryID = 2;

DELETE FROM dbo.Orders OUTPUT deleted.* WHERE OrderID = 5;

6.5 MERGE (Upsert)

MERGE has known edge-case bugs and race conditions. Use HOLDLOCK and test thoroughly. Many teams prefer separate UPDATE and INSERT statements.

MERGE dbo.Products AS tgt
USING dbo.ProductStaging AS src
   ON tgt.ProductID = src.ProductID
WHEN MATCHED AND tgt.UnitPrice <> src.UnitPrice THEN
    UPDATE SET tgt.UnitPrice = src.UnitPrice
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductName, CategoryID, UnitPrice) VALUES (src.ProductName, src.CategoryID, src.UnitPrice)
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET tgt.IsActive = 0
OUTPUT $action, inserted.ProductID, deleted.ProductID;

6.6 Bulk Loading

BULK INSERT dbo.Customers
FROM 'C:\Data\customers.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n',
      TABLOCK, BATCHSIZE = 50000);

SELECT * FROM OPENROWSET(BULK 'C:\Data\customers.csv', FORMAT = 'CSV', FIRSTROW = 2,
                         FORMATFILE = 'C:\Data\cust.fmt') AS x;

bcp ShopDB.dbo.Customers out customers.dat -S localhost -T -n
bcp ShopDB.dbo.Customers in  customers.dat -S localhost -T -n -b 10000