SQL Declare Variable with Insert Statement
In this example you will learn How to make an INSERT INTO query with variables in SQL Server? We’ll go back to the first tutorial and modify it slightly to use variables in the INSERT INTO query instead of hard-coding the values.
How to write an INSERT INTO query using T-SQL variables?
1. Create the CLIENTS table with the NAME column declared as unique so that you do not insert the same client name twice. The NOCLIENT client number is auto-incremented to have a list followed by customer numbers.
|
1 2 3 4 5 6 7 8 |
IF exists( SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[dbo].[CLIENTS]') AND type in (N'U') ) DROP TABLE [dbo].[CLIENTS]; CREATE TABLE [dbo].[CLIENTS] ( [NOCLIENT] int IDENTITY(1,1), [NAME] nvarchar(20) UNIQUE, [CITY] nvarchar(20) |
2. In T-SQL code block, declare a variable for each column. Each of the data types declared in the variables corresponds to the data types of the columns. Insert the data with a simple INSERT INTO query that uses the two variables declared above.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Variable declaration DECLARE @NAME NVARCHAR(20); DECLARE @CITY NVARCHAR(20); -- First insertion SET @NAME = 'BROWN'; SET @CITY = 'LA'; INSERT INTO dbo.CLIENTS ( NAME, CITY) VALUES ( @NAME, @CITY); -- Second insertion SET @NAME= 'CHRISTOPHE'; SET @CITY= 'WDC'; INSERT INTO dbo.CLIENTS ( NAME, CITY) VALUES ( @NAME, @CITY); |
Note that using variables in T-SQL is especially useful if you reuse the value of the variable multiple times.
