SQL Server INSERT INTO with variables to manage dynamic inserts

Use the SQL Server Insert Into query with variables to insert data dynamically.

How to build a SQL Server INSERT INTO statement with variables to make the code dynamic? This tutorial is like the first in the series for inserting data with SQL Server from a select query. To do this, modify the code slightly to create the table and use variables in the query instead of hard coding the values to be inserted.

1. SQL INSERT INTO query with T-SQL variables

This tutorial shows how to use variables in a query to insert data into a SQL Server table, reuse the code in this Insert Into example from a SELECT query, and add T-SQL variables to it.

2. Create the SQL table to insert the data

First, create the Customers table with the NAME column declared as unique so that the same customer is not inserted twice. The customer number NOCLIENT is auto incremented to have a continuous list of customer numbers.

IF EXISTS(
  SELECT 1 FROM sys.objects
  WHERE object_id = object_id(N'[dbo].[CLIENTS]')
  AND type in (N'U')
)
DROP TABLE [dbo].[CLIENTS];
GO

CREATE TABLE [dbo].[CLIENTS] (
  [NOCLIENT] int IDENTITY(1,1),
  [NAME] nvarchar(20) UNIQUE,
  [CITY] nvarchar(20)
);
GO

3. INSERT INTO query to insert data with variables

Indeed, in a block of T-SQL code, follow these two simple steps. Declare a variable for each column. Each of the data types declared in the variables corresponds to the data types of the columns. Then insert the data with an INSERT INTO query that uses the two variables declared at the beginning of the code.

-- Declare the variables
DECLARE @NAME NVARCHAR(20);
DECLARE @CITY NVARCHAR(20);

-- First insertion
SET @NAME = 'MAMMADOU';
SET @CITY = 'Lyon';
INSERT INTO dbo.CLIENTS ( NAME, CITY ) VALUES ( @NAME, @CITY );

-- Second insertion
SET @NAME = 'AHMED';
SET @CITY = 'Paris';
INSERT INTO dbo.CLIENTS ( NAME, CITY ) VALUES ( @NAME, @CITY );

4. Advantages of variables in T-SQL development

The use of T-SQL variables is useful to reuse the value of the variable several times. To avoid copying and pasting the same values in a script and therefore to factorise the objects as much as possible. Making T-SQL code dynamic is a good practice, highly recommended, it is notably the main purpose of SQL Server functions and stored procedures.

This article explains how to build and execute a dynamic SQL Server script with an INSERT INTO command and variables. And thus, insert data in a table via variables instantiated at the beginning of the script, or which evolve during the course of the script. Finally, to go further, here is a tutorial to update the same column of another row in a SQL Server table.