{"id":18878,"date":"2022-06-08T07:12:00","date_gmt":"2022-06-08T05:12:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=18878"},"modified":"2022-11-30T12:01:51","modified_gmt":"2022-11-30T11:01:51","slug":"sql-server-insert-into-with-variables","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-insert-into-with-variables\/","title":{"rendered":"SQL Server INSERT INTO with variables to manage dynamic inserts"},"content":{"rendered":"\n

Use the SQL Server Insert Into query with variables to insert data dynamically.<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

1. SQL INSERT INTO query with T-SQL variables<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

2. Create the SQL table to insert the data<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

IF EXISTS(\n  SELECT 1 FROM sys.objects\n  WHERE object_id = object_id(N'[dbo].[CLIENTS]')\n  AND type in (N'U')\n)\nDROP TABLE [dbo].[CLIENTS];\nGO\n\nCREATE TABLE [dbo].[CLIENTS] (\n  [NOCLIENT] int IDENTITY(1,1),\n  [NAME] nvarchar(20) UNIQUE,\n  [CITY] nvarchar(20)\n);\nGO<\/pre>\n\n\n\n
3. INSERT INTO query to insert data with variables<\/h2>\n\n\n\n
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<\/a> that uses the two variables declared at the beginning of the code.
<\/p>\n\n\n\n
-- Declare the variables\nDECLARE @NAME NVARCHAR(20);\nDECLARE @CITY NVARCHAR(20);\n\n-- First insertion\nSET @NAME = 'MAMMADOU';\nSET @CITY = 'Lyon';\nINSERT INTO dbo.CLIENTS ( NAME, CITY ) VALUES ( @NAME, @CITY );\n\n-- Second insertion\nSET @NAME = 'AHMED';\nSET @CITY = 'Paris';\nINSERT INTO dbo.CLIENTS ( NAME, CITY ) VALUES ( @NAME, @CITY );\n<\/pre>\n\n\n\n
4. Advantages of variables in T-SQL development<\/h2>\n\n\n\n
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<\/a> is a good practice, highly recommended, it is notably the main purpose of SQL Server functions and stored procedures.<\/p>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
\n
Update the same column of another line with SQL Server<\/a><\/blockquote>