An empty database, well… it’s not very exciting, it’s not very useful. To make it useful, we need to populate it with information.
The command responsible for adding new rows to a table is INSERT INTO.
It seems like a simple operation, but doing it correctly is important to maintain data integrity (because saving data haphazardly doesn’t help, of course).
Basic Syntax
The fundamental way to insert a record requires defining two things:
INSERT INTO TableName (Column1, Column2, Column3)
VALUES (Value1, Value2, Value3);
- In which table we are going to write.
- In which columns we are going to put data.
- What values we are going to save.
For example, let’s assume we have our Customers table.
INSERT INTO Customers (FirstName, LastName, Email, RegistrationDate)
VALUES ('Luis', 'Llamas', '[email protected]', '2023-10-01');
The order of the values must exactly match the order of the columns we have listed.
- The first value (‘Luis’) goes to the first column (
FirstName). - The second value (‘Llamas’) goes to the second (
LastName), - And so on.
Insert Without Specifying Columns
T-SQL allows an abbreviated syntax where we do not list the columns, assuming we are going to provide values for all of them in the order they were created.
-- ⛔ BAD PRACTICE: Inserting without defining columns
INSERT INTO Customers
VALUES ('Luis', 'Llamas', '[email protected]', '2023-10-01');
Don’t do that. Always specify the column list explicitly.
If tomorrow someone modifies the table and adds a new column at the beginning, or changes the column order, it will insert the data into the wrong column.
Handling Special Columns
It is not always necessary (or possible) to provide a value for all columns in the table. For example in these cases.
IDENTITY Columns
If we have an ID column defined as IDENTITY(1,1), SQL Server generates the number automatically. We cannot include it in our INSERT.
-- Assuming CustomerID is IDENTITY
INSERT INTO Customers (FirstName, LastName) -- We skip CustomerID
VALUES ('Ana', 'García');
-- SQL Server will assign the ID automatically (e.g., 101)
Columns with DEFAULT or that allow NULL
If a column has a DEFAULT value or allows nulls, we can omit it from the list.
-- We omit RegistrationDate, so SQL will use the current date (DEFAULT)
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Carlos', 'Pérez', '[email protected]');
Multiple Insertion Bulk Insert
What if we want to insert 10 users? In the old days, we had to execute 10 separate INSERT statements. This is inefficient because it implies 10 trips to the server.
Since SQL Server 2008, we can insert multiple rows in a single statement, separating the value tuples with commas.
INSERT INTO Customers (FirstName, LastName, Email)
VALUES
('Laura', 'Méndez', '[email protected]'),
('Pedro', 'Sánchez', '[email protected]'),
('Sofía', 'Ruiz', '[email protected]');
This is much faster and is treated as a single atomic transaction (either all are inserted, or none are).
Insert Data from Another Table INSERT INTO SELECT
Sometimes we don’t want to insert literal values, but copy data from one table to another. For example, moving data from a Candidates table to Employees when they are hired, or archiving old data.
For this we combine INSERT INTO with SELECT.
INSERT INTO Employees (FirstName, LastName, Email)
SELECT FirstName, LastName, WorkEmail
FROM Candidates
WHERE Status = 'Hired';
In this case:
- The
SELECTfinds all hired candidates. - The result of that query is inserted directly into the
Employeestable. - There’s no need to use
VALUES.
There is another command called SELECT ... INTO NewTable. The difference is that
INSERT INTO ... SELECTrequires the destination table to already existSELECT INTOcreates the new table on the fly
Could they have made it more complicated? Honestly, it’s difficult 🙄
What Happens if an INSERT Fails?
The INSERT can fail for several reasons related to the constraints (Constraints) we saw in Module 2:
- Trying to insert a duplicate into a
PRIMARY KEYorUNIQUEcolumn. - Trying to insert a
NULLinto aNOT NULLcolumn. - Inserting a value that violates a
FOREIGN KEY(e.g., an order for a non-existent customer). - Inserting data with the wrong type (e.g., text in an INT field).
If any of these errors occur, SQL Server will cancel the operation and the row will not be saved.
