CRUD Operations

  -- Single row
INSERT INTO dbo.Users (Name, Email, Status)
VALUES (N'Alice', N'[email protected]', N'active');

-- Multiple rows
INSERT INTO dbo.Users (Name, Email, Status)
VALUES
    (N'Bob', N'[email protected]', N'active'),
    (N'Carol', N'[email protected]', N'pending');

-- Insert from query
INSERT INTO dbo.ArchiveUsers (UserID, Name, Email, ArchivedAt)
SELECT UserID, Name, Email, SYSUTCDATETIME()
FROM dbo.Users
WHERE Status = N'inactive';
  

  INSERT INTO dbo.Orders (CustomerID, Total)
VALUES (42, 149.99);

SELECT SCOPE_IDENTITY() AS new_order_id;  -- Same session/scope
-- Prefer OUTPUT for triggers/multiple inserts:
INSERT INTO dbo.Orders (CustomerID, Total)
OUTPUT INSERTED.OrderID
VALUES (42, 149.99);
  

  SELECT UserID, Name, Email, CreatedAt
FROM dbo.Users
WHERE Status = N'active'
  AND CreatedAt >= '2024-01-01'
ORDER BY CreatedAt DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
  

  -- IN and BETWEEN
SELECT * FROM dbo.Products WHERE CategoryID IN (1, 3, 5);
SELECT * FROM dbo.Orders WHERE Total BETWEEN 50.00 AND 200.00;

-- Pattern matching — leading wildcard prevents index use
SELECT * FROM dbo.Users WHERE Email LIKE N'%@example.com';  -- Scan likely
SELECT * FROM dbo.Users WHERE Email LIKE N'[email protected]';  -- Seek possible
  

  DECLARE @last_id INT = 1000;

SELECT TOP 20 OrderID, CustomerID, Total, CreatedAt
FROM dbo.Orders
WHERE OrderID > @last_id
ORDER BY OrderID;
  

  -- Simple update
UPDATE dbo.Users
SET Status = N'inactive', ModifiedAt = SYSUTCDATETIME()
WHERE LastLoginAt < '2023-01-01';

-- Conditional update with safety
UPDATE dbo.Products
SET Stock = Stock - 1
WHERE ProductID = 42 AND Stock > 0;

IF @@ROWCOUNT = 0
    RAISERROR(N'Insufficient stock or product not found', 16, 1);
  

  UPDATE o
SET o.DiscountPct = 10, o.ModifiedAt = SYSUTCDATETIME()
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = N'US' AND o.Status = N'Pending';
  

  SELECT o.OrderID, o.DiscountPct
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE c.Country = N'US' AND o.Status = N'Pending';
  

  -- Simple delete
DELETE FROM dbo.Sessions WHERE ExpiresAt < SYSUTCDATETIME();

-- Join delete
DELETE oi
FROM dbo.OrderItems oi
INNER JOIN dbo.Orders o ON oi.OrderID = o.OrderID
WHERE o.Status = N'cancelled';
  

  UPDATE dbo.Users SET IsDeleted = 1, DeletedAt = SYSUTCDATETIME()
WHERE UserID = 100;
  

  MERGE dbo.Settings AS target
USING (VALUES (N'theme', N'dark'), (N'locale', N'en-US')) AS source (KeyName, Value)
ON target.KeyName = source.KeyName
WHEN MATCHED AND target.Value <> source.Value THEN
    UPDATE SET Value = source.Value, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (KeyName, Value) VALUES (source.KeyName, source.Value)
WHEN NOT MATCHED BY SOURCE AND target.KeyName LIKE N'temp_%' THEN
    DELETE;
  

  -- Audit inserts
INSERT INTO dbo.Users (Name, Email)
OUTPUT INSERTED.UserID, INSERTED.Name, INSERTED.Email, N'INSERT' AS action
INTO dbo.UserAudit (UserID, Name, Email, Action)
VALUES (N'Dave', N'[email protected]');

-- Capture deleted rows
DELETE FROM dbo.TempImport
OUTPUT DELETED.ImportID, DELETED.RawData, SYSUTCDATETIME()
INTO dbo.TempImportArchive (ImportID, RawData, DeletedAt);
  

  BEGIN TRANSACTION;

BEGIN TRY
    INSERT INTO dbo.Orders (CustomerID, Total) VALUES (1, 99.99);
    DECLARE @order_id INT = SCOPE_IDENTITY();

    INSERT INTO dbo.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@order_id, 10, 2, 29.99), (@order_id, 11, 1, 49.99);

    UPDATE dbo.Customers SET OrderCount = OrderCount + 1 WHERE CustomerID = 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
  

  BULK INSERT dbo.StagingProducts
FROM 'C:\import\products.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK,
    BATCHSIZE = 10000
);
  

  -- Step 1: Staging table with IDs to update
CREATE TABLE #to_update (UserID INT PRIMARY KEY);
INSERT INTO #to_update SELECT UserID FROM dbo.Users WHERE LastLoginAt < '2022-01-01';

-- Step 2: Batch update
WHILE 1 = 1
BEGIN
    UPDATE TOP (1000) u
    SET Status = N'archived'
    FROM dbo.Users u
    INNER JOIN #to_update t ON u.UserID = t.UserID
    WHERE u.Status <> N'archived';

    IF @@ROWCOUNT = 0 BREAK;
    WAITFOR DELAY '00:00:01';  -- Reduce blocking
END;