Insert Data into a Table Using SQL Insert Statement
- MikeBennyhoff
- Feb 14, 2024
- 11 min read
Syntax of SQL INSERT statement
The following SQL statement or INSERT statement is used to add new records (rows) adding values to specific columns in a table. Here’s the basic syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Let’s break down the components:
INSERT INTO: This is where syntax insert the keyword that indicates you want to insert data into a table.
table_name: The name of the table you want to insert data into.
(column1, column2, …): Optional list of columns you want to insert data into. If you specified columns, you need to provide values for each of these columns. If omitted, values for all columns must be provided in the same order as they appear in the table.
VALUES: This keyword is used to specify the values and data types you want to insert values into the table.
(value1, value2, …): The values you want to insert into the table command the specified three columns in the table. The number of values must match the number of columns specified, or match the total number of columns in the table if no columns are specified.
Here’s a simple example:
INSERT INTO Students (FirstName, LastName, Age, Grade)
VALUES ('John', 'Doe', 18, 'B');
This statement inserts a new record into both the column names first column of the “Students” table with default values of ‘John’ for the FirstName column, ‘Doe’ for the LastName column, 18 for the Age column, and ‘B’ for the Grade column.
The INSERT statement in SQL Server has evolved over different versions of the software, introducing new features and enhancements. Here’s a brief overview of the different versions and their notable features related to the INSERT statement:
SQL Server 2000:
Introduced the basic INSERT INTO syntax for adding new rows to a table.
Supported the INSERT INTO SELECT statement for inserting data from one table into another based on a SELECT query.
Allowed inserting data into tables with identity columns.
SQL Server 2005:
Introduced the OUTPUT clause for capturing the results of an INSERT, UPDATE, DELETE, or MERGE statement.
Added support for inserting multiple rows using a single INSERT INTO statement with multiple value lists.
Introduced the ROW_NUMBER() function, which allowed for generating row numbers for inserted rows.
SQL Server 2008:
Introduced the MERGE statement, which combines INSERT, UPDATE, and DELETE operations into a single statement based on a specified condition.
Added support for table-valued parameters, allowing inserting multiple rows into a table using a single parameterized INSERT INTO statement.
SQL Server 2012:
Introduced the SEQUENCE object, which allows for generating sequence numbers that can be used in INSERT statements.
Added support for the OFFSET FETCH clause, which allows for paging through result sets, useful for batching inserts.
SQL Server 2016:
Introduced the support for JSON data format, allowing for inserting JSON data into SQL Server tables.
Added support for the STRING_SPLIT function, which splits a string into rows of substrings based on a specified separator.
SQL Server 2019:
Introduced the support for the APPROX_COUNT_DISTINCT function, which provides an approximate count of distinct values, useful for faster inserts and queries on large datasets.
Added support for the INSERT INTO … VALUES clause with the DEFAULT keyword, allowing for explicitly inserting default values into columns.
These are some of the significant versions of SQL Server and the features related to the INSERT statement introduced in each version. Each version has brought improvements and new capabilities to the INSERT statement, enhancing its functionality and performance.
Simple SQL Insert statement
Let’s use an automotive example to demonstrate the INSERT INTO syntax.
Suppose we have a table called “Cars” with columns for the make, model, year, and price of each car. Here’s how you can use the INSERT INTO statement to both add values to new records statement to insert the values to this table:
Inserting a Single Record:
CREATE TABLE Cars (
CarID INT PRIMARY KEY AUTO_INCREMENT,
Make VARCHAR(50),
Model VARCHAR(50),
Year INT,
Price DECIMAL(10, 2) Null
);
INSERT INTO Cars (Make, Model, Year, Price)
VALUES ('Toyota', 'Camry', 2022, 25000);
This statement inserts a new record into the “Cars” table with the following query make ‘Toyota’, model ‘Camry’, year 2022, and price $25,000.
Inserting Multiple Records:
INSERT INTO Cars (Make, Model, Year, Price)
VALUES
('Honda', 'Civic', 2021, 22000),
('Ford', 'Mustang', 2020, 35000),
('Chevrolet', 'Silverado', 2019, 30000);
This statement inserts multiple records into the “Cars” table. Each row in customers table specifies the make, model, year, and price of a car. In this example, we’re inserting records for a Honda Civic, Ford Mustang, and Chevrolet Silverado.
Inserting Records with NULL Values:
INSERT INTO Cars (Make, Model, Year)
VALUES ('Tesla', 'Model S', 2023);
In this example, we’re inserting a record for a Tesla Model S into the “Cars” table. We’re not providing a value for the “Price” column, so it will default to NULL.
Inserting Records with Explicit Column Names:
INSERT INTO Cars (Make, Model)
VALUES ('BMW', 'X5');
If you don’t specify values for all columns, you need to explicitly list all the columns that you’re inserting data into. In this example, we’re just inserting values for a record for a BMW X5 into the “Cars” rows of data table, without specifying the year or price.
These examples demonstrate how to use the INSERT INTO statement to add new records to a table, using an automotive example with new rows in the “Cars” table.
Insert With Select Statement
Let’s create a new table called “CarModels” and demonstrate how to use the INSERT INTO statement with a SELECT query to insert data from one existing table name, the “Cars” table into the “CarModels” table.
-- Create the CarModels table
CREATE TABLE CarModels (
ModelID INT PRIMARY KEY AUTO_INCREMENT,
ModelName VARCHAR(50),
Make VARCHAR(50),
Year INT
);
-- Insert data into the CarModels table from the Cars table
INSERT INTO CarModels (ModelName, Make, Year)
SELECT Model, Make, Year
FROM Cars;
In this example:
We first create the “CarModels” table with columns for the model ID, model name, make, and year of each car model. The ModelID column is defined as the primary key with auto-increment.
Then, we use the INSERT INTO statement with a SELECT query to insert data into the “CarModels” table from the “Cars” table. The SELECT query retrieves the model, make, and year from the “Cars” table.
We don’t explicitly specify the values for the ModelID column because it’s auto-incremented and will generate unique column values almost automatically.
This INSERT INTO statement with a SELECT query allows us to populate new row in the “CarModels” table with data from the “Cars” table. Each row in the “CarModels” table will represent a car model extracted from the “Cars” table.
Select Info
The SELECT INTO statement is used to create a new table based on the result set of a SELECT query. Here’s how you can use it to insert existing records back into a new table:
-- Create a new table "NewCars" and insert data into it from the "Cars" table
SELECT *
INTO NewCars
FROM Cars;
In this example:
We use the SELECT INTO statement to create a new table called “NewCars” and insert data into it from the “Cars” table.
The following query: SELECT * retrieves all columns and rows from the “Cars” table.
The INTO keyword specifies that the result set should be inserted into a new table called “NewCars”.
After executing this statement, a new table “NewCars” will be created with the same structure and same data type as the “Cars” table in previous example.
It’s important to note that the SELECT INTO statement creates a new table based on the result set of the SELECT query and doesn’t require the “NewCars” table to exist beforehand. If the “NewCars” table already exists, this statement will result in an error.How to Insert Multiple Records with the INSERT Statement
Select Into with Temp Table
-- Create a new table "NewCars" and insert data into it from the "Cars" table
SELECT *
INTO #NewCars
FROM Cars;
Inserting data in specific columns
How to Insert Records from a SELECT Statement with a WHERE Clause, an ORDER BY Clause, a LIMIT Clause, and an OFFSET Clause
Inserting data returned from an OUTPUT clause
Let’s use the same “Cars” table data and demonstrate how to insert data returned from one table statement an OUTPUT clause into another table statement.
Suppose we have a table called “SoldCars” where we want to insert records for cars that have been sold. We’ll use an OUTPUT clause to capture the inserted data and then insert it into the “SoldCars” table.
-- Create the SoldCars table
CREATE TABLE SoldCars (
SaleID INT PRIMARY KEY AUTO_INCREMENT,
Make VARCHAR(50),
Model VARCHAR(50),
Year INT,
Price DECIMAL(10, 2)
);
-- Insert data into the SoldCars table and capture the inserted data using OUTPUT clause
INSERT INTO SoldCars (Make, Model, Year, Price)
OUTPUT inserted.*
INTO SoldCars
SELECT Make, Model, Year, Price
FROM Cars
WHERE Make = 'Toyota' AND Year = 2022;
In this example:
We first create the “SoldCars” table with columns inserting rows for the sale ID, make, model, year, and price of each sold car.
Then, we use the INSERT INTO statement with an OUTPUT clause to capture the inserted data.
The OUTPUT clause specifies inserted.*, which means we want to capture all columns of the inserted rows.
We use the INTO keyword to specify that the output data should be inserted into the “SoldCars” table.
The SELECT query retrieves data from the “Cars” table where the make is ‘Toyota’ and the year is 2022.
Only the records that meet the specified conditions will be inserted into the “SoldCars” table, and the inserted data will also be returned as a result of the OUTPUT clause.
Inserting data into a table with columns that have default values
Let’s create a new table with a default value for the “Age” column and then insert data into first table in it:
-- Create a new table with a default value for the "Age" column
CREATE TABLE DefaultAgeTable (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT DEFAULT 30
);
-- Insert data into the DefaultAgeTable, omitting the "Age" column
INSERT INTO DefaultAgeTable (ID, Name)
VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Michael');
-- Verify the inserted data
SELECT * FROM DefaultAgeTable;
In this example:
We create a new table called “DefaultAgeTable” with three rows and columns for ID, Name, and Age. The Age column has a default value of 30 specified.
When we insert data into the “DefaultAgeTable”, we omit specifying a value for the Age column. Since the Age column has a default value defined, the database system will automatically assign that default value to the Age column for each inserted row.
After executing the INSERT INTO statement, we use a SELECT query to verify that the data has been inserted correctly into the “DefaultAgeTable”
Row_Number function With Insert statement
In T-SQL, the ROW_NUMBER() function is typically used in the SELECT statement and queries to generate row numbers for result sets. It’s not directly used in INSERT statements. However, you can use a CTE (Common Table Expression) with the ROW_NUMBER() function to assign row numbers to the rows you’re inserting. Here’s an example:
Let’s say we have a table called “Employees” with columns for EmployeeID, FirstName, and LastName. We want to insert rows of new employees into this table, but we also want to assign a unique EmployeeID to insert multiple rows for each new employee. We can use the ROW_NUMBER() function to generate these unique IDs:
-- Example of using ROW_NUMBER in an INSERT statement
INSERT INTO Employees (EmployeeID, FirstName, LastName)
SELECT
ROW_NUMBER() OVER (ORDER BY FirstName, LastName) + (SELECT ISNULL(MAX(EmployeeID), 0) FROM Employees),
FirstName,
LastName
FROM
NewEmployees; -- NewEmployees is a table or query that contains the new employee data
In this example:
We use the SELECT query with the ROW_NUMBER() function to generate row numbers for each row in the result set.
We use the ROW_NUMBER() function with the OVER clause to specify the ordering of the rows. You can order the rows based on any column(s) or expression(s) you want.
We then add the generated row number to the maximum EmployeeID in the Employees table to ensure uniqueness.
Finally, we insert the generated EmployeeID, FirstName, and LastName into the Employees table.
This approach allows you to insert multiple new rows into a table while generating unique IDs for each row using the ROW_NUMBER() function.
String Split Insert statement
The STRING_SPLIT function in T-SQL allows you to split a string into a table of substrings based on a specified separator. Here’s an example of how you can use STRING_SPLIT in an INSERT statement:
Let’s say we have a table called “Tags” with a single column “Tag” where we want to insert tags for a post. We have a string of tags separated by commas that we want to insert into this table.
-- Example of using STRING_SPLIT in an INSERT statement
DECLARE @TagsString VARCHAR(MAX) = 'SQL, T-SQL, Database';
INSERT INTO Tags (Tag)
SELECT value
FROM STRING_SPLIT(@TagsString, ',');
In this example:
We declare a variable @TagsString and assign it a string containing multiple tags separated by commas.
We use the STRING_SPLIT function to split the @TagsString into individual substrings based on the comma separator.
We then use the SELECT query to retrieve the individual substrings (tags) generated by STRING_SPLIT.
Finally, we insert these individual substrings (tags) into the “Tags” table.
After executing this INSERT statement, the “Tags” table will contain separate rows for each tag extracted from the @TagsString variable. Each row in source table will contain a single tag in the “Tag” column.
Column Names And Values Both
The OFFSET FETCH clause in T-SQL is used for paging through result sets. It allows you to skip a specified number of rows from the beginning of the result set and then return a specified number of rows after that. This can be useful for implementing pagination in applications, where you want to display data in chunks or pages.
Here’s an example of how to use OFFSET FETCH:
Suppose we have a table called “Products” with columns for ProductID, ProductName, and Price. We want to retrieve a paginated list of products sorted by ProductID.
-- Example of using OFFSET FETCH for pagination
SELECT ProductID, ProductName, Price
FROM Products
ORDER BY ProductID
OFFSET 10 ROWS -- Skip the first 10 rows
FETCH NEXT 5 ROWS ONLY; -- Fetch the next 5 rows
In this example:
We use the OFFSET clause to skip the first 10 rows of the result set.
We use the FETCH NEXT clause to fetch the next 5 rows after skipping the offset rows.
The result set will contain rows 11 through 15 of the sorted Products table.
When might this be useful?
Pagination: As mentioned earlier, OFFSET FETCH is commonly used for implementing pagination in web applications. It allows you to retrieve data in chunks or pages, improving performance by fetching only the necessary rows.
Displaying subsets of data: If you have a large result set and you want to display only a portion of it at a time, OFFSET FETCH allows you to fetch subsets of data based on specified criteria.
Analyzing trends: You can use OFFSET FETCH to analyze trends or patterns in your data by fetching subsets of data for analysis.
Overall, OFFSET FETCH is useful whenever you need to work with result sets in chunks or pages, or when you need to retrieve subsets of data for analysis or display purposes.
Inserting With JSON
In SQL Server, you can use JSON data format to insert data into tables by converting JSON objects into rows. Here’s an example:
Let’s say we have a table called “Employees” with columns for EmployeeID, FirstName, LastName, and DepartmentID. We want to insert new employees into this table using JSON format.
-- Example of inserting data into a table using JSON in T-SQL
DECLARE @EmployeeData NVARCHAR(MAX) = '
[
{"FirstName": "John", "LastName": "Doe", "DepartmentID": 1},
{"FirstName": "Jane", "LastName": "Smith", "DepartmentID": 2},
{"FirstName": "Michael", "LastName": "Johnson", "DepartmentID": 1}
]';
INSERT INTO Employees (FirstName, LastName, DepartmentID)
SELECT
JSON_VALUE(EmployeeData, '$.FirstName') AS FirstName,
JSON_VALUE(EmployeeData, '$.LastName') AS LastName,
JSON_VALUE(EmployeeData, '$.DepartmentID') AS DepartmentID
FROM OPENJSON(@EmployeeData);
In this example:
We declare a variable @EmployeeData and assign it a JSON string containing information about new employees.
We use the OPENJSON function to parse the JSON string and convert it into a tabular format. OPENJSON returns a table with columns for each property in the JSON objects.
We use JSON_VALUE function to extract values from the JSON objects and insert them into the Employees table.
The SELECT query retrieves the FirstName, LastName, and DepartmentID values from the JSON objects returned by OPENJSON.
Finally, we insert the extracted values into the Employees table.
After executing this INSERT statement, the “Employees” table will contain the new employees specified in the JSON string. Each row in customers table will represent a new employee with their respective FirstName, LastName, and DepartmentID.
Additional Resources
Here is a short video on Insert with SQL Server
Comments