SQL Server : how to insert using variable

Question

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'.

Not sure, but maybe try saving @val nvarchar(50) = ' test ' give space before and after test — Sudipta Mondal
– Sudipta Mondal, Commented Jan 16, 2018 at 7:19
Basic debugging : print or select the string after building it but before you execute it. You'll see you didn't quote your VARCHAR values. Also, look into SP_EXECUTESQL which allows typed parameters for dynamic sql, then you don't need to quote it, and won't be vulnerable to SQL Injection Attacks. — MatBailie
– MatBailie, Commented Jan 16, 2018 at 8:24

Squirrel · Accepted Answer · 2018-01-16 07:19:42Z

5

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)

answered Jan 16, 2018 at 7:19

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis Rubashkin · Accepted Answer · 2018-01-16 08:04:39Z

2

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

answered Jan 16, 2018 at 8:04

Denis Rubashkin

2,2311 gold badge11 silver badges16 bronze badges

1 Comment

MatBailie Over a year ago

This is absolutely the correct way to go. It protects from attacks, it protects from mistakes, deals with quoting and allows execution plan reuse. The other answers work, this is correct.

Ullas · Accepted Answer · 2018-01-16 07:33:08Z

1

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);

edited Jan 16, 2018 at 7:33

answered Jan 16, 2018 at 7:27

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Comments

Pawan Kumar · Accepted Answer · 2018-01-16 08:10:26Z

1

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)

answered Jan 16, 2018 at 8:10

Pawan Kumar

2,02112 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Server : how to insert using variable

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related