CODEWITHSUNDEEP
sqlsql-server
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

SQL Server : how to insert using variable

I am trying to insert data into a SQL Server table using a variable. I tried

DECLARE @table NVARCHAR(50) = 'ToolList',
        @val NVARCHAR(50) = 'test'

EXEC ('INSERT INTO ' + @table + 'SELECT ' + @val)

and

EXEC ('INSERT INTO ' + @table + '([col1]) VALUES(' + @val +')'

but still get an error that says

Incorrect syntax near 'test'.

Upvotes: 0

Views: 1130

Answers (4)

Pawan Kumar
Pawan Kumar

Reputation: 2011

You need 4 singlequotes before the @val field as it is a string and all strings needs to be encapsulated in single quotes. You can print the dynamic string using PRINT command check what the final string you are going to execute.

DECLARE @table VARCHAR(50) = 'ToolList'
DECLARE @val VARCHAR(50) = 'test'
DECLARE @DSQL AS VARCHAR(MAX) = ''

SET @DSQL = @DSQL + ' INSERT INTO [' + @table + ']' + ' 
 SELECT ' + '''' + @val + ''''

--PRINT @DSQL
EXEC(@DSQL)

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

You'd better use sp_executesql that allows for statements to be parameterized, to avoid the risk of SQL injection.

DECLARE @Query NVARCHAR(1000),
    @table NVARCHAR(50) = 'ToolList'

SET @Query = 'INSERT INTO ' + @table + ' SELECT @val'
EXEC sp_executesql @Query, N'@val nvarchar(50)', @val = 'test'

sp-executesql-transact-sql

Upvotes: 2

Ullas
Ullas

Reputation: 11556

You can also use CHAR(39) instead of adding single quotes every time for better readability. And also, you have not added a space after the variable which contains the table name.

Query

declare @table nvarchar(50) = 'ToolList',
        @val nvarchar(50) = 'test2';

 declare @sql as varchar(max) = 'insert into ' + @table 
                             + ' select ' + char(39) +  @val + char(39);
exec(@sql);

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

you missed a space before SELECT and the @val should enclosed in single quote

DECLARE @table nvarchar(50) = 'ToolList',
    @val nvarchar(50) = 'test'


EXEC ( 'INSERT INTO ' + @table + ' SELECT ''' + @val + '''')

when you use Dynamic SQL, it is easier to form the query in a variable so that you can print out , inspect the value before execution

select @sql = 'INSERT INTO ' + @table + ' SELECT ''' + @val + ''''
print  @sql
exec  (@sql)

Upvotes: 5

Related Questions