Loading Data with the INSERT Statement in SQL Server 2025

The INSERT statement is the most direct way to load data into a SQL Server table, and it works the same way regardless of where that table actually lives. Windows, Linux, an Azure virtual machine, Azure SQL Database, and Azure SQL Managed Instance all run the same T-SQL INSERT statement, with the same syntax and the same behavior. A handful of real platform differences do exist in this lesson, mostly around bulk loading, and they're covered where they actually matter, but INSERT itself isn't one of them. This statement forms a core part of data manipulation in both the transactional workloads this series has already built, like SalesDB, and the denormalized, analytical workloads this module's first lesson introduced.

The most explicit form of INSERT names every column being filled in:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Here, table_name is the target table, the column list says exactly which columns are being filled in, and the values list supplies one value per listed column, in the same order. If values are supplied for every column, in the table's own column order, the column list can be omitted entirely:

INSERT INTO table_name
VALUES ('A', 100, GETDATE());

Leaving the column list off is a habit worth avoiding outside of quick, throwaway scripts. It works today, but it silently breaks the moment someone adds a column to the table or reorders one, since the statement has no way of knowing your intent beyond raw positional order; the error you get back, if you get one at all, often has nothing to do with the actual column that moved.

SQL Server also applies implicit conversion where it reasonably can, so inserting the string '100' into an int column, or a string date into a datetime column, generally just works without an explicit cast. That's a convenience, not a license to be careless about types; a conversion that can't succeed, text that isn't actually numeric going into a numeric column, fails the whole statement rather than silently inserting something wrong, which is the safer failure mode but still one worth avoiding by matching your data types deliberately rather than relying on the conversion to bail you out.

INSERT doesn't require a literal list of values at all. You can insert the result of a query directly, pulling rows from one table, or several joined together, straight into another:

INSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;

This is the pattern behind a lot of real data movement work: feeding a denormalized reporting table from a transactional one, migrating a subset of rows between databases, or populating a new table as part of the kind of ETL process this module's first lesson introduced. Against a database like SalesDB, this might mean pulling only last quarter's orders into an archive table:

INSERT INTO SalesOrdersArchive (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM SalesOrders
WHERE OrderDate < DATEADD(QUARTER, -1, GETDATE());

or copying a filtered set of customer records into a staging table before a larger transformation runs against it. Either way, the source and target tables don't need matching column names, only a matching number of columns in a compatible order; it's the column list on each side, not the names themselves, that determines what maps to what.

For loads that need to either insert a new row or update an existing one depending on whether it's already there, the MERGE statement, mentioned briefly in this module's first lesson, is the tool built for that specific job. It's a more involved statement than plain INSERT, and it's worth knowing it exists even though this lesson doesn't cover its syntax in depth; plain INSERT is the right tool once you know every row is genuinely new.

If a column has a default constraint and isn't listed in the INSERT statement, SQL Server uses the default rather than leaving the column empty. If no default exists and the column allows NULL, a NULL goes in instead:

CREATE TABLE Products (
    ProductID INT IDENTITY,
    ProductName NVARCHAR(100),
    Quantity INT DEFAULT 10
);

INSERT INTO Products (ProductName)
VALUES ('Widget'); -- Quantity defaults to 10

IDENTITY columns, already covered earlier in this series, auto-increment on their own, and you normally don't insert a value into one at all. On the rare occasion you need to, loading a specific set of known IDs during a migration, for instance, IDENTITY_INSERT has to be turned on for that table first, and back off again afterward:

SET IDENTITY_INSERT table_name ON;
-- insert statement with an explicit ID value
SET IDENTITY_INSERT table_name OFF;

The more common question that comes up right after an ordinary insert into an IDENTITY column is simpler: what value did SQL Server actually generate? SCOPE_IDENTITY() returns the most recently generated identity value within the same session and the same scope, which is almost always what you want immediately after an insert, as opposed to @@IDENTITY, an older function that can return a value from an unrelated trigger instead and is generally best avoided for that reason. IDENT_CURRENT('table_name') is a third option, returning the last identity value generated for a specific table regardless of session or scope, useful mainly when you genuinely don't have a session-scoped value to work with, though it's also the least safe of the three in a busy system, since another session's insert can change the answer between when you call it and when you act on it. The OUTPUT clause offers a more flexible alternative to all three, returning the actual inserted row, including its new identity value, directly from the INSERT statement itself:

INSERT INTO Products (ProductName)
OUTPUT INSERTED.ProductID, INSERTED.ProductName
VALUES ('Widget');

When a single logical load actually involves more than one INSERT, an order going into one table and its line items going into another, wrapping both in a transaction keeps them atomic:

BEGIN TRANSACTION;
    INSERT INTO Orders (...) VALUES (...);
    INSERT INTO OrderItems (...) VALUES (...);
COMMIT TRANSACTION;

If something goes wrong partway through, ROLLBACK in place of COMMIT undoes everything in the transaction rather than leaving the order inserted with no line items to go with it. Every one of these statements is also recorded in the transaction log regardless of whether you wrap them in an explicit transaction yourself, SQL Server treats a single, unwrapped INSERT as its own implicit transaction, which is part of why the recovery model decisions covered earlier in this series matter for load performance: a database in the full recovery model logs considerably more detail per insert than one in the simple recovery model, a cost worth remembering for a particularly large load.

In real application code, that transaction is usually paired with error handling rather than left to fail silently:

BEGIN TRY
    BEGIN TRANSACTION;
        INSERT INTO Orders (...) VALUES (...);
        INSERT INTO OrderItems (...) VALUES (...);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

TRY...CATCH catches the error instead of letting it propagate unhandled, the CATCH block rolls the transaction back cleanly, and THROW re-raises the original error afterward so the calling application still finds out something went wrong, rather than the failure disappearing silently into a rolled-back transaction nobody hears about.

TOP works inside an INSERT ... SELECT the same way it works in an ordinary SELECT, useful for loading a sample or a batch rather than an entire result set at once:

INSERT INTO target_table (col1, col2)
SELECT TOP (10) col1, col2 FROM source_table
ORDER BY col1;

The ORDER BY clause in that example isn't decorative. Without it, TOP returns whichever rows SQL Server happens to retrieve first, which is not guaranteed to be consistent from one run to the next; if which specific ten rows you get actually matters, and it usually does, an explicit ORDER BY is what makes that choice deterministic rather than incidental.

Most of what's covered so far behaves identically everywhere SQL Server 2025 runs. Bulk loading is the one place that genuinely isn't true across every environment. On Windows, Linux, or an Azure virtual machine, BULK INSERT reads directly from a file on local or network disk, exactly as covered in this module's discussion of bulk-loading tools.

Azure SQL Database doesn't give you that local file system access at all, so a traditional disk-based BULK INSERT simply isn't an option there. Instead, the source file has to sit in Azure Blob Storage, referenced through an external data source secured with a shared access signature:

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<storageaccount>.blob.core.windows.net/container',
    CREDENTIAL = MyBlobCredential
);

BULK INSERT table_name
FROM 'file.csv'
WITH (
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2
);

The external data source and its credential only need to be created once per database; after that, any BULK INSERT against that blob container simply references it by name. If you're working against Azure SQL Database specifically, that's the detail to remember; everywhere else, including Azure SQL Managed Instance and SQL Server on an Azure virtual machine, the local or network-file version already covered applies without any of this extra setup.

Either way, running BULK INSERT takes more than ordinary table permissions, as this module's first lesson already noted: membership in the bulkadmin fixed server role, or the ADMINISTER BULK OPERATIONS server permission, on top of the usual INSERT permission on the target table itself.

A handful of row-by-row INSERT statements never needs special handling, but once you're loading thousands of rows through plain INSERT rather than BULK INSERT, a few habits make a real difference. Batching the work, inserting a few thousand rows per transaction rather than one enormous transaction or one transaction per row, tends to be the sweet spot: one transaction per row pays the overhead of starting and committing a transaction thousands of times over, while one transaction for the entire load holds locks and grows the transaction log for the whole duration, which can cause its own problems if something else needs that table in the meantime.

Indexes are the other major cost. Every index on a table has to be updated as part of every insert into it, so a table with several indexes pays that cost on each row loaded. For a genuinely large one-time load, it's a common and reasonable pattern to disable non-clustered indexes before the load and rebuild them afterward, the same technique this course's second lesson mentioned in the context of OLAP-style ETL processing, rather than paying the index-maintenance cost on every single row as it comes in.

None of this is a reason to avoid plain INSERT for ordinary work. A few rows, or even a few thousand, inserted the normal way causes none of these problems; they only start to matter once volume climbs into the range where BULK INSERT, bcp, or an SSIS package, all covered elsewhere in this module, would usually be the better tool to reach for in the first place. Knowing where that line sits is as much a part of this lesson as the syntax itself.

Outside of an ad hoc query window, INSERT statements built from application code should always be parameterized rather than assembled from concatenated strings. Building a statement by pasting user input directly into the SQL text, something like concatenating a username straight into an INSERT string, is exactly how SQL injection happens: user input that was meant to be a value gets interpreted as part of the command instead. Parameterizing the same statement closes that door, since the value is passed separately from the statement's structure and can never be reinterpreted as SQL:

-- In application code:
INSERT INTO Users (Username, Email)
VALUES (@Username, @Email);

Always Encrypted and row-level security, both available regardless of platform, add further protection around what gets inserted and who can see it afterward, building directly on the permissions work covered earlier in this series. Neither one changes how you write an INSERT statement; they change what SQL Server does with the data and who's allowed to see it once it's there. Always Encrypted keeps sensitive column values encrypted both at rest and in transit, decrypting them only on a client that holds the right key, so even a login with full read access to the table never sees the plaintext value without that key. Row-level security works differently, filtering which rows a given login can see or affect at all, through a predicate function tied to the table, which matters for an INSERT specifically if that same predicate is configured to block inserts that don't match it, failing the statement before the row is ever written rather than merely hiding it afterward.

Monitoring a load in progress doesn't call for new tools beyond what this course has already introduced. sys.dm_exec_requests, covered in this course's first module, shows an insert that's currently running and how long it's taken so far, and on Azure SQL Database specifically, sys.dm_db_resource_stats adds CPU, I/O, and memory figures scoped to that one database. Query Store, also already covered, captures how an INSERT ... SELECT performs over repeated runs the same way it tracks any other query, which matters if the same load runs on a schedule and you want to know whether it's gotten slower over time. A dedicated, deeper look at Extended Events, DMVs, and the rest of this course's monitoring toolkit belongs to the monitoring-focused lessons already covered earlier in this course rather than to a lesson about the INSERT statement specifically; what's worth remembering here is simply that none of those tools treat an insert any differently from any other query. If a large load is running slower than expected, those same tools, not a different set built specifically for loading, are where you'd look first: sys.dm_os_wait_stats to see what the session is actually waiting on, and Activity Monitor for a quick visual check of whether the load is blocked behind something else entirely.

The next lesson covers SELECT INTO, a related but distinct way of loading data that creates its destination table automatically from a query's result, rather than requiring the table to already exist the way INSERT does. Between the two, you'll have covered the two most common starting points for getting data into a table at all, before this module moves on to the higher-volume tools, BULK INSERT, bcp, and SSIS, that build on the same basic ideas at a larger scale.