Dynamic SQL INSERT VALUES SQL 2008

Question

Working on a project that requires a procedure with a dynamic table name. Need to work out how to write an insert value query into the table. Not a massive project so overheads and such is not an issue.

Tried several variations however it always falls over.

DECLARE @DynamicTableName NVARCHAR(100)
SET @DynamicTableName = 'DynamicTableName'


    INSERT @DynamicTableName
    (
        HolidayStartDate
      , HolidayEndDate
      , HolidayType
      , HolidayTypeID
      , StaffCode
      , StaffName
      , Notes
      , FTE
      , CreatedBy
      , CreatedDate
      , HolidayRequestID
    )
    VALUES
    (       @Para1,
            @Para2,
            @Para3,
            @Para4,
            @Para5,
            @Para5,
            @Para6,
            @Para7,  
            @Para8,
            @Para9, 
            @Para10
    )

Not gonna work, an object identifier may not be referenced via a variable. Construct the SQL within a string and call sp_executeSQL, this supports input parameters. — Alex K.
– Alex K., Commented Nov 29, 2016 at 13:04

John Cappelletti · Accepted Answer · 2016-11-29 13:16:39Z

1

You can drop the Para1-n into a #Temp table and then perform a dynamic insert.

The benefit here is that you don't have parse or quote parameters

DECLARE @DynamicTableName NVARCHAR(100)
SET @DynamicTableName = 'DynamicTableName'

Select  P1=@Para1,P2=@Para2,P3=@Para3,P4=@Para4,P5=@Para5,P6=@Para6,P7=@Para7,P8=@Para8,P9=@Para9,P10=@Para10 Into TempInsert 
Declare @SQL varchar(max) = '
Insert Into '+@DynamicTableName +'(
        HolidayStartDate
      , HolidayEndDate
      , HolidayType
      , HolidayTypeID
      , StaffCode
      , StaffName
      , Notes
      , FTE
      , CreatedBy
      , CreatedDate
      , HolidayRequestID
    ) Select * From #TempInsert'
Exec(@SQL)

EDIT - I should note that I see only 10 parameters yet you have 11 fields.

edited Nov 29, 2016 at 13:16

answered Nov 29, 2016 at 13:10

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

Comments

Mansoor · Accepted Answer · 2016-11-29 13:07:59Z

0

DECLARE @TableName NVARCHAR(100),@QueryStr NVARCHAR(MAX) = ''

SET @QueryStr = 'INSERT INTO ' +@TableName+ '(     
HolidayStartDate,HolidayEndDate,HolidayType, HolidayTypeID, StaffCode
, StaffName, Notes, FTE, CreatedBy, CreatedDate, HolidayRequestID)
VALUES ('+ @Para1 +','+ @Para2 +','+ @Para3 +','+ @Para4 +','+ @Para5 +','+  
@Para5 +','+ @Para6 +','+ @Para7 +','+ @Para8 +','+ @Para9 +','+ @Para10 +')'

 EXEC (@QueryStr)

answered Nov 29, 2016 at 13:07

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

1 Comment

Alex K. Over a year ago

sp_executeSQL is a better choice as it will escape text within the variables if they are treated as parameters, EXEC() can lead to injection vulnerabilities.

Collectives™ on Stack Overflow

Dynamic SQL INSERT VALUES SQL 2008

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related