SQL Server
INSERT Basics in SQL Server
Learn how to insert one or several rows in SQL Server with explicit columns, clear values, and a SELECT check of the result.
On this page
After learning to read rows with your first SELECT, the next step is learning to add a row. SQL Server uses INSERT to add new data to a table. This lesson uses only the synthetic JaviBuildsLab database and its dbo.Customers table. A successful INSERT changes the table, so inspect the destination and verify the result instead of assuming the statement worked.
Insert one row with a column list
The demo table has customer IDs 1 through 10 in its setup script. The following practice row uses ID 11, which is outside that seeded range. First confirm the database context and whether that key is free:
SELECT DB_NAME() AS CurrentDatabase;
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE CustomerId = 11;For the INSERT to be appropriate, the first query should show JaviBuildsLab and the second should return no rows. Then add the row:
INSERT INTO dbo.Customers
(
CustomerId,
FirstName,
LastName,
City,
State,
Email,
CreatedDate
)
VALUES
(
11,
N'Maya',
N'Rivera',
N'Orlando',
'FL',
N'maya.rivera@example.com',
'2026-10-21'
);INSERT INTO names the target table. The parenthesized list names the columns that receive values, and VALUES supplies one value for each listed column in the same order. The N before a string literal marks it as Unicode text, matching the table’s NVARCHAR columns. The state column is CHAR(2), so 'FL' fits. The date is written as year-month-day, a clear form for this example.
This ID is a practice value, not a new seed row. Run the example once in your local practice database. If you run it again, the primary key will already exist; check first and choose an unused practice ID rather than changing the setup script. The setup remains deterministic and additive, and no customer record is added automatically by the website.
Why list the columns?
You may see a shorter form such as INSERT INTO dbo.Customers VALUES (...). It depends on remembering every table column and its exact physical order. If a schema changes, or if a reader miscounts values, the statement can fail or place a valid value in the wrong column. An explicit list documents what the statement intends to supply and aligns each value directly with a name. It also allows you to omit columns that accept NULL or have a real default.
Column lists help during review, too. A teammate can see that City receives Orlando and CreatedDate receives the date without looking up the table definition. Keep the column and value lists in the same order. SQL Server maps them positionally; it does not match each value to a column by meaning.
Omit a nullable column when appropriate
In the demo schema, Email allows NULL and has no default. You can leave it out when adding a customer whose email is not known. SQL Server supplies NULL for that omitted nullable column:
INSERT INTO dbo.Customers
(CustomerId, FirstName, LastName, City, State, CreatedDate)
VALUES
(12, N'Rosa', N'Chen', N'Phoenix', 'AZ', '2026-10-21');This is a separate practice example using ID 12. It does not imply a default email address. An omitted nullable column becomes NULL because of the table definition. An omitted NOT NULL column without a defined default would cause the insert to fail. Check the schema rather than guessing what SQL Server will fill in.
Add a small set of rows
One INSERT can add more than one row. Every row needs one value per listed column, and each value must be compatible with its column:
INSERT INTO dbo.Customers
(CustomerId, FirstName, LastName, City, State, Email, CreatedDate)
VALUES
(13, N'Noah', N'Patel', N'Boulder', 'CO', N'noah.patel@example.com', '2026-10-21'),
(14, N'Leila', N'Morgan', N'Raleigh', 'NC', NULL, '2026-10-21');This is another alternative practice statement, not a setup-script change. The second row uses the SQL value NULL for an unknown email. NULL is not text, so write NULL, not 'NULL'. Quoted text 'NULL' would be four letters and is not the same value. The earlier NULL lesson explains how SQL Server compares and displays missing values. Do not run every illustrative INSERT repeatedly; use a fresh ID only after checking that it is available.
Match values to SQL data types
Text values go in single quotes. Prefix Unicode text for NVARCHAR with N, as in N'Maya'. Numeric values such as an integer key are written without quotes: 11, not '11'. SQL Server can convert some text automatically, but relying on implicit conversion makes mistakes harder to see. Dates should use an unambiguous literal such as '2026-10-21'. For missing information, use the unquoted keyword NULL only when the column allows it.
Each table column also has a type and often a length. FirstName and LastName allow up to 50 Unicode characters; State allows two characters. Values that are too long or cannot convert to the target type can be rejected or truncated depending on the context and settings. Keep examples within the declared limits and check actual column definitions when adapting them.
Identity columns and constraints
An IDENTITY column is configured to generate numeric values as rows are inserted. Many tables use one for a surrogate key, so the INSERT normally leaves that column out. The demo’s CustomerId is not an IDENTITY column: it is an INT NOT NULL primary key that the INSERT must supply. Do not assume a table generates IDs unless its schema says so. The existing setup script does not need to change for this lesson.
Constraints protect the table from invalid relationships and values. CustomerId is a primary key, so inserting a duplicate ID fails. In dbo.Orders, CustomerId is a foreign key to dbo.Customers; an order for a customer ID that does not exist is rejected. A NULL value cannot go into a NOT NULL column, and a value of the wrong type or length can also cause an error. These checks are useful: they prevent a row that violates the table’s rules from silently becoming valid data.
When an INSERT reports an error, read which table, column, or constraint is named. Verify the database, inspect the destination columns, and compare the values to the explicit list. Correct the cause before retrying. Avoid responding to a key conflict by changing unrelated seeded data; choose a free practice key instead.
Verify the new row
After the statement completes, read the row back using its key:
SELECT
CustomerId,
FirstName,
LastName,
City,
State,
Email,
CreatedDate
FROM dbo.Customers
WHERE CustomerId = 11;Confirm that the returned values match the row you intended to add. If no row appears, check the message in SSMS, confirm that the INSERT ran in the expected database, and confirm that the selected ID is correct. This is the same habit used when filtering rows with WHERE: a focused condition makes it easier to inspect a specific record.
SQL Server also supports INSERT ... SELECT, which inserts rows returned by a query. It is useful when copying or transforming a set of rows, but it combines reading and writing and deserves careful review of both the source query and destination list. For now, learn the explicit VALUES form first; the same column-matching discipline applies to the later pattern.
An INSERT adds rows to the table; it does not update an existing row just because the key matches. If the key is already present, the primary key constraint rejects the duplicate. Check whether the row exists before deciding whether your task is to add a new record or change an existing one. The two tasks use different statements and different safety checks. For an existing record, use the SELECT-first workflow in UPDATE basics, rather than trying to solve a duplicate-key error by altering another customer’s key.
When you insert multiple rows, count the value groups and compare each one with the column list. If there are seven listed columns, every value tuple needs seven entries, including an explicit NULL where an optional field is unknown. Review the entire set before running it: one malformed tuple can prevent the intended insert from completing. Afterward, SELECT the inserted IDs and confirm all expected rows are present. This small review becomes more useful as an INSERT grows beyond a single example row.
Common mistakes and practice
Common mistakes include leaving out the column list, putting values in a different order, quoting NULL, reusing a primary key, inserting into the wrong database, and treating a completed query window as proof that the intended row exists. Check the current database, use a clear destination list, and SELECT the new row by its key. Remember that an INSERT is a real change, even in a demo database.
Practice with JaviBuildsLab. Before each insert, verify the database and choose an unused ID. The examples are independent; they are not intended to be run repeatedly as a batch.
- Add customer 15 named Imani Brooks in Boise, Idaho, without an email.
- Add two customers with IDs 16 and 17 in one statement; give one a NULL email.
- Which columns are required if no defaults exist? Check the table definition and identify the
NOT NULLcolumns. - Why does the demo INSERT supply
CustomerIdwhile many identity-based tables omit their key? - Write a verification query for customer 15.
Answers
-- 1
INSERT INTO dbo.Customers (CustomerId, FirstName, LastName, City, State, CreatedDate)
VALUES (15, N'Imani', N'Brooks', N'Boise', 'ID', '2026-10-21');
-- 2
INSERT INTO dbo.Customers (CustomerId, FirstName, LastName, City, State, Email, CreatedDate)
VALUES
(16, N'Eli', N'Park', N'Juneau', 'AK', N'eli.park@example.com', '2026-10-21'),
(17, N'Sara', N'Bell', N'Madison', 'WI', NULL, '2026-10-21');
-- 5
SELECT CustomerId, FirstName, LastName, Email
FROM dbo.Customers
WHERE CustomerId = 15;For exercise 3, the listed CustomerId, FirstName, LastName, and CreatedDate are NOT NULL; the other customer fields can be NULL. Exercise 4 depends on the actual schema: this demo assigns the key itself. Once the row is present, continue with UPDATE basics and learn to preview the target before changing it.
- New values
- INSERT INTO dbo.Customers
- New row added
- SELECT verification
- Confirm inserted row
Return to the SQL Server Foundations roadmap whenever you want to see your place in the learning path.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 16 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetCheck Index UsageReview SQL Server index read and write activity, identify low-value indexes, and decide what deserves deeper analysis before any index is removed.Open cheat sheet
- RoadmapSQL Server FoundationsLearn how to insert one or several rows in SQL Server with explicit columns, clear values, and a SELECT check of the result.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.