Create SQL Server table name that includes a variable value

Question

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]

Since you can't do this directly, you can either 1. Use dynamic SQL or 2. Create the table without the suffix and then use sp_rename to rename it — George Menoutis
– George Menoutis, Commented Sep 5, 2022 at 5:42
Creating a table inside a trigger seems like a really BAD idea .... — marc_s
– marc_s, Commented Sep 5, 2022 at 6:03

Charlieface · Accepted Answer · 2022-09-05 10:54:17Z

2

You can use dynamic sql to do this. Like below

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

Set @PlanID = 123456

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

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

exec (@sql);

edited Sep 5, 2022 at 10:54

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

answered Sep 5, 2022 at 6:03

Nagib Mahfuz

83310 silver badges21 bronze badges

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

1 Comment

Sean Baker Over a year ago

This did exactly what I wanted. I appreciate your help immensely. Now to test any and all scenarios to see if I run into any unforeseen issues.

Collectives™ on Stack Overflow

Create SQL Server table name that includes a variable value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related