Insert Data

Every database starts empty. Before any query can return results, before any report can be generated, before any application can function, data must be inserted. The INSERT statement is the gateway — the only way to add new rows to a table. It looks straightforward, but there is a significant difference between INSERT statements that are safe, readable, and maintainable and those that silently break when the schema changes, fail unexpectedly under concurrent load, or leave related tables in an inconsistent state. This lesson covers every form of the INSERT statement, the rules that govern insertion order in a schema with foreign keys, and the techniques that make bulk inserts both fast and reliable — using the DataplexaStore tables throughout.

Single Row INSERT

The standard INSERT statement specifies the target table, a list of column names, and a corresponding list of values. The column list is not technically required — SQL Server will accept an INSERT without it and match values to columns by ordinal position — but omitting the column list is one of the most common sources of production bugs. If a column is added, removed, or reordered in the table definition, a column-list-free INSERT silently puts values in the wrong columns or fails with a cryptic type error. Always name your columns explicitly. The values in the VALUES clause must match the listed columns in count and compatible data type, in the same order. IDENTITY columns and columns with DEFAULT constraints should be omitted from both lists — SQL Server fills them automatically.

-- Insert a single user — always specify the column list explicitly
-- UserID is omitted because it is an IDENTITY column — SQL Server generates it
-- MembershipTier and JoinDate are omitted — their DEFAULT constraints supply values

INSERT INTO Users (FullName, Email, City, Country)
VALUES ('Alice Johnson', 'alice.johnson@example.com', 'New York', 'USA');

-- Insert with all non-identity columns specified — overriding the MembershipTier default
INSERT INTO Users (FullName, Email, City, Country, MembershipTier)
VALUES ('Bob Williams', 'bob.williams@example.com', 'London', 'UK', 'Premium');

-- Confirm both rows were inserted with correct values
SELECT UserID, FullName, Email, MembershipTier, JoinDate
FROM Users
WHERE Email IN ('alice.johnson@example.com', 'bob.williams@example.com');

• Alice received MembershipTier = 'Standard' automatically from the DEFAULT constraint — no application code needed to supply it
• Bob's explicit 'Premium' value overrides the DEFAULT — when a column is present in the INSERT list with a value, the DEFAULT is ignored entirely
• JoinDate was populated by GETDATE() on both rows — the timestamp captured at the moment each INSERT executed, not when the table was created

Multi-Row INSERT

SQL Server supports inserting multiple rows in a single INSERT statement by providing multiple value tuples separated by commas. This is significantly faster than individual INSERT statements for two reasons: it generates a single transaction log entry covering all rows rather than one per row, and it reduces the round trips between the application and the database server. The entire multi-row INSERT is atomic — either all rows are inserted successfully or none are. If any single row violates a constraint the entire statement is rolled back, leaving the table unchanged. This all-or-nothing behaviour is the correct default for inserting logically related sets of rows.

-- Insert all DataplexaStore seed data in dependency order
-- Products and Users first (no foreign keys), then Orders, OrderItems, Reviews

-- Insert remaining Users
INSERT INTO Users (FullName, Email, City, Country, MembershipTier)
VALUES
    ('Clara Davis',    'clara.davis@example.com',    'Toronto',      'Canada',    'VIP'),
    ('David Kim',      'david.kim@example.com',      'Seoul',        'South Korea','Standard'),
    ('Eva Martinez',   'eva.martinez@example.com',   'Madrid',       'Spain',     'Premium'),
    ('Frank Chen',     'frank.chen@example.com',     'Shanghai',     'China',     'Standard'),
    ('Grace Patel',    'grace.patel@example.com',    'Mumbai',       'India',     'VIP'),
    ('Henry Osei',     'henry.osei@example.com',     'Accra',        'Ghana',     'Standard'),
    ('Isla Nguyen',    'isla.nguyen@example.com',    'Ho Chi Minh',  'Vietnam',   'Premium'),
    ('James Wilson',   'james.wilson@example.com',   'Sydney',       'Australia', 'Standard');

-- Insert all Products
INSERT INTO Products (ProductName, Category, Price, StockQty)
VALUES
    ('Wireless Mouse',        'Electronics',   29.99,  42),
    ('Mechanical Keyboard',   'Electronics',   89.99,  15),
    ('USB-C Hub',             'Electronics',   49.99,  30),
    ('Webcam HD',             'Electronics',   79.99,  18),
    ('Desk Lamp',             'Home Office',   34.99,  55),
    ('Standing Desk',         'Home Office',  349.99,   8),
    ('Ergonomic Chair',       'Home Office',  249.99,  12),
    ('Notebook A5',           'Stationery',    12.99, 120),
    ('Ballpoint Pen Set',     'Stationery',     8.99, 200),
    ('Monitor 27 inch',       'Electronics',  399.99,  10);

-- Verify row counts across both tables
SELECT 'Users' AS table_name, COUNT(*) AS row_count FROM Users
UNION ALL
SELECT 'Products',            COUNT(*)              FROM Products;

• Both inserts are single statements covering multiple rows — one transaction log record per statement rather than one per row, which is meaningfully faster at scale
• If any of the 8 user rows violated the UNIQUE constraint on Email, the entire INSERT would fail and no users would be inserted — the atomic all-or-nothing behaviour protects consistency
• The dependency rule is enforced here by order: Users and Products exist before the next section inserts Orders that reference Users

Inserting Dependent Rows — Following the Foreign Key Chain

In a schema with foreign key constraints, rows must be inserted in dependency order. A child row cannot reference a parent row that does not yet exist. For DataplexaStore this means Orders must be inserted after Users, OrderItems after both Orders and Products, and Reviews after both Users and Products. This ordering is not just a convention — it is enforced by the database engine. Attempting to insert an Order before the referenced User exists produces a foreign key violation error immediately, and the INSERT is rejected. The correct approach is to follow the dependency chain strictly: parents before children, always.

-- Insert Orders — each references an existing UserID
INSERT INTO Orders (UserID, TotalAmount, Status)
VALUES
    (1,  1029.98, 'delivered'),
    (2,    89.99, 'delivered'),
    (3,   159.98, 'shipped'),
    (1,   399.99, 'processing'),
    (4,    42.98, 'delivered');

-- Insert OrderItems — each references an existing OrderID and ProductID
INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES
    (1, 10, 2, 399.99),   -- Order 1: 2x Monitor
    (1,  1, 1,  29.99),   -- Order 1: 1x Wireless Mouse
    (1,  3, 1,  49.99),   -- Order 1: 1x USB-C Hub
    (1,  2, 1,  89.99),   -- Order 1: 1x Mechanical Keyboard
    (2,  2, 1,  89.99),   -- Order 2: 1x Mechanical Keyboard
    (3,  5, 2,  34.99),   -- Order 3: 2x Desk Lamp
    (3,  8, 7,  12.99),   -- Order 3: 7x Notebook
    (4, 10, 1, 399.99),   -- Order 4: 1x Monitor
    (5,  1, 1,  29.99),   -- Order 5: 1x Wireless Mouse
    (5,  8, 1,  12.99);   -- Order 5: 1x Notebook

-- Insert Reviews — each references an existing UserID and ProductID
INSERT INTO Reviews (UserID, ProductID, Rating, ReviewText)
VALUES
    (1,  1, 5, 'Best wireless mouse I have ever used.'),
    (2,  2, 4, 'Great keyboard, a little loud though.'),
    (3, 10, 5, 'Stunning monitor, colours are incredible.'),
    (4,  5, 3, 'Decent lamp but the base wobbles slightly.'),
    (5,  8, 5, 'Perfect notebook, great paper quality.');

-- Final row count across all tables
SELECT 'Users'      AS table_name, COUNT(*) AS rows FROM Users      UNION ALL
SELECT 'Products',                  COUNT(*)         FROM Products   UNION ALL
SELECT 'Orders',                    COUNT(*)         FROM Orders     UNION ALL
SELECT 'OrderItems',                COUNT(*)         FROM OrderItems UNION ALL
SELECT 'Reviews',                   COUNT(*)         FROM Reviews;

• 40 rows across 5 tables inserted in strict dependency order — this is the complete DataplexaStore seed dataset used throughout the rest of the course
• UserID 1 (Alice) has two orders — Order 1 with four items and Order 4 with one item — demonstrating a realistic one-to-many relationship between Users and Orders
• Order 1's TotalAmount of 1029.98 matches the sum of its four OrderItems: (2 × 399.99) + 29.99 + 49.99 + 89.99 = 1029.95 — a small discrepancy that reflects a real-world scenario where totals are sometimes calculated at order creation and not kept in sync, a data quality issue you will explore in later lessons

INSERT ... SELECT

The INSERT ... SELECT form inserts rows sourced from a query result rather than literal values. It is the standard tool for copying data between tables, loading staging tables, archiving old records, or populating a new table from existing data. The SELECT query can reference any tables, apply any filtering, and perform any transformations — the result set is inserted row by row into the target table. The column list rules are the same: always name your target columns explicitly and ensure the SELECT column list matches in count and compatible type. INSERT ... SELECT is also atomic — if any row from the SELECT result violates a constraint, the entire operation rolls back.

-- Create a summary table for high-value orders and populate it with INSERT...SELECT

CREATE TABLE HighValueOrders (
    OrderID         INT             NOT NULL,
    UserFullName    NVARCHAR(100)   NOT NULL,
    TotalAmount     DECIMAL(10,2)   NOT NULL,
    Status          NVARCHAR(20)    NOT NULL,
    CapturedAt      DATETIME2       NOT NULL DEFAULT GETDATE(),

    CONSTRAINT PK_HighValueOrders PRIMARY KEY (OrderID)
);

-- Populate from existing data — only orders above 200
INSERT INTO HighValueOrders (OrderID, UserFullName, TotalAmount, Status)
SELECT
    o.OrderID,
    u.FullName,
    o.TotalAmount,
    o.Status
FROM Orders o
JOIN Users u ON o.UserID = u.UserID
WHERE o.TotalAmount > 200                   -- filter applied in the SELECT, not the INSERT
ORDER BY o.TotalAmount DESC;                -- rows inserted in descending amount order

SELECT * FROM HighValueOrders;

• CapturedAt was not in the INSERT column list — its DEFAULT GETDATE() filled it automatically at the moment the INSERT ran, stamping every row with the same capture time
• The JOIN and WHERE in the SELECT are evaluated first, and only the filtered result set is passed to the INSERT — INSERT ... SELECT is just a regular SELECT feeding its output to an INSERT
• ORDER BY in INSERT ... SELECT determines the physical insertion order but does not guarantee storage order — row retrieval order is controlled by queries, not by insertion order

SELECT INTO — Creating and Populating in One Step

SELECT INTO creates a new table and populates it in a single statement, inferring the column definitions from the SELECT column list. It is useful for quickly creating working copies of data for analysis, staging tables, or development snapshots. The new table inherits column names and data types from the source but does not inherit any constraints — no primary key, no foreign keys, no indexes, no defaults. For a permanent production table always use CREATE TABLE followed by INSERT ... SELECT so you can define constraints explicitly. SELECT INTO is appropriate for temporary analysis tables and development work where speed matters more than a formally defined schema.

-- SELECT INTO creates the table and inserts the data in one statement
-- Useful for quick snapshots and analysis copies

SELECT
    p.ProductID,
    p.ProductName,
    p.Category,
    p.Price,
    COUNT(oi.OrderItemID)           AS times_ordered,
    SUM(oi.Quantity)                AS total_units_sold
INTO ProductSalesSummary            -- new table created here with inferred column types
FROM Products p
LEFT JOIN OrderItems oi ON p.ProductID = oi.ProductID
GROUP BY p.ProductID, p.ProductName, p.Category, p.Price;

-- Verify the new table and its contents
SELECT * FROM ProductSalesSummary
ORDER BY total_units_sold DESC;

-- Clean up — drop the temporary summary table
DROP TABLE ProductSalesSummary;
DROP TABLE HighValueOrders;

• SELECT INTO inferred all column types from the source query — ProductID as INT, ProductName as NVARCHAR, Price as DECIMAL, the aggregated columns as INT — no explicit type declarations needed
• Products with 0 orders appear because of the LEFT JOIN — an INNER JOIN would have excluded unordered products, hiding them from the analysis
• The Notebook A5 is the most ordered product by volume (8 units across 2 orders) — a query pattern you will use extensively in the SELECT Queries lesson

Summary Table

Practice Questions

Quiz

Next up - Select Queries - Query the DataplexaStore data you just inserted and learn every clause that shapes a SELECT statement.