Create SQL Server table name that includes a variable value-CodePudding

I'm creating a SQL Server table via a trigger, and I want the table name to be specific each time.

For the end result, I want the table name to be tblTEMP_INA_DATA_12345.

I could obviously, just type tblTEMP_INA_DATA_12345, but the @PlanID value will be different each time.

How could I modify the create table statement to do what I want? Is this possible?

I have searched, but I'm not sure what search terms to even use. I appreciate any and all responses even if the answer is no.

DECLARE @PlanID varchar(80)

SET @PlanID = 12345

CREATE TABLE [dbo].[tblTEMP_INA_DATA_]
(
    [strQuestion] [varchar](max) NULL,
    [strAnswer] [varchar](max) NULL
) ON [PRIMARY]

CodePudding user response：

You can use dynamic sql to do this. Like below

Declare @PlanID varchar(80),@sql varchar(max);

Set @PlanID = 123456

set @sql= 'Create TABLE [dbo].[tblTEMP_INA_DATA_' @PlanID ']
([strQuestion] [varchar](max) NULL,

[strAnswer] [varchar](max) NULL
)  ON [PRIMARY]'

exec (@sql);