SQL Server 2014 – How to Use Variable Table Name in INSERT INTO Statement

sql serversql server 2014t-sql

I want to insert into Tables but error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'A'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'B'.

My table names is : TBL_PriceKeyBoard
Keyboard is variable and maybe hard or ram but TBL_Price is static.

Declare @sql as nvarchar(max)
Declare  @TBL as nvarchar(max)
Declare @Name_Edit  nvarchar(max) 
Declare @Name1 as nvarchar(max)= N'B'
Declare @Price as nvarchar(max) = N'12'
Declare @NameTBL  as nvarchar(max)  = N'KeyBoard'



set @Name_Edit = N'A'
 set @TBL = N'TBL_Price'+ @NameTBL  
Set @sql = N'INSERT INTO [DB1].[dbo].'+quotename(@TBL) +'
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             '+@Name_Edit+'
            ,'+@Name1+'
            ,'+@Price+'
            )'
            EXECUTE sp_executesql @sql

Best Answer

You're not quoting your strings properly.

When you insert a string, it has to be surrounded by single quotes, which in Dynamic SQL-ese, is three ticks. This should work.

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @TBL AS NVARCHAR(MAX);
DECLARE @Name_Edit NVARCHAR(MAX); 
DECLARE @Name1 AS NVARCHAR(MAX)= N'B';
DECLARE @Price AS NVARCHAR(MAX) = N'12';
DECLARE @NameTBL AS NVARCHAR(MAX)  = N'KeyBoard';

SET @Name_Edit = N'A';
SET @TBL = N'TBL_Price' + @NameTBL;  
SET @sql = N'INSERT INTO [DB1].[dbo].' + QUOTENAME(@TBL) + '
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             ''' + @Name_Edit + '''
            ,''' + @Name1 + '''
            ,''' + @Price + '''
            )';
              PRINT ( @sql );
            --EXECUTE sp_executesql @sql

When I print the code, I get this.

INSERT INTO [DB1].[dbo].[TBL_PriceKeyBoard]
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             'A'
            ,'B'
            ,'12'
            )
Related Question