Create a table using a variable as the table name

Question

I need to create a table where the name will be the value of 4 variables and some string.

I've found various questions/answers, but nothing that really matches what I'm after. Ultimately this will be used as embedded SQL on an eForm, but for the time being I'm trying to achieve this directly in MS SQL Server Management Studio.

This is what I have:

DECLARE @INSFIRSTNAME VARCHAR (100) = 'Ted'
DECLARE @INSSURNAME VARCHAR (100) = 'Smith'
DECLARE @INSSTAFFNO VARCHAR (10) = 'AB123'
DECLARE @INSYEAR VARCHAR (10) = '2018'
DECLARE @TABLENAME VARCHAR (400) = 'Timesheet_' + @INSFIRSTNAME + @INSSURNAME + @INSSTAFFNO + '_' + @INSYEAR

CREATE TABLE @TABLENAME
             (INSFIRSTNAME VARCHAR (100),
              INSSURNAME VARCHAR (100),
              INSSTAFFNO VARCHAR (10),
              INSYEAR VARCHAR (10));
INSERT INTO   @TABLENAME
              (INSFIRSTNAME, INSSURNAME, INSSTAFFNO, INSYEAR)
VALUES        (@INSFIRSTNAME, @INSSURNAME, @INSSTAFFNO, @INSYEAR)

This throws up the message:

"Incorrect syntax near '@TABLENAME'.

I've tried various ways of populating the TABLENAME variable, but nothing has worked. From what I've read dynamic SQL appears to be the answer, but I haven't found a way to achieve this. Any help will be very much appreciated.

To be honest, I think this is a bad design. Why don't you just use a timesheets table and user id's the refer to a normalized user table? — Rob
– Rob, Commented Jul 20, 2017 at 8:04
@Francis why are you asking for this? If you want to create a deployment script, use sqlcmd scripts, like those produced by SSMS or SSDT with database and table variables. SQLCMD understands what these are so there is no risk of SQL injection — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2017 at 8:30
Building different tables per .. business entity? Is also a very bad design. You can use a single table and partition it IF you have a lot of data. If you want to use separate tables per customer use different schemas or even databases. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2017 at 8:32
Why don't you use a single timesheet table? No matter how many employees a company has, there are only so many days in a year. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2017 at 8:36
I've been having a chat around the office and come to the same conclusion - a single table is the way to go. I've squirrelled away the solution supplied for future reference, but will be developing this as suggested. Many thanks. — Francis
– Francis, Commented Jul 20, 2017 at 9:36

Andrea · Accepted Answer · 2017-07-20 08:16:47Z

1

You can use dynamic TSQL:

DECLARE @INSFIRSTNAME VARCHAR (100) = 'Ted'
DECLARE @INSSURNAME VARCHAR (100) = 'Smith'
DECLARE @INSSTAFFNO VARCHAR (10) = 'AB123'
DECLARE @INSYEAR VARCHAR (10) = '2018'
DECLARE @TABLENAME VARCHAR (400) = 'Timesheet_' + @INSFIRSTNAME + @INSSURNAME + @INSSTAFFNO + '_' + @INSYEAR

--declare a variable that will hold your query
declare @sql_create nvarchar(max)

--create the query concatenating DDL instructions and variables
set @sql_create=''
set @sql_create= @sql_create + 'CREATE TABLE ' + @TABLENAME 
set @sql_create= @sql_create + ' (INSFIRSTNAME VARCHAR (100),'
set @sql_create= @sql_create + '  INSSURNAME VARCHAR (100),'
set @sql_create= @sql_create + '  INSSTAFFNO VARCHAR (10),'
set @sql_create= @sql_create + '  INSYEAR VARCHAR (10));'

set @sql_create= @sql_create + ' INSERT INTO ' + @TABLENAME
set @sql_create= @sql_create + '               (INSFIRSTNAME, INSSURNAME, INSSTAFFNO, INSYEAR)'
set @sql_create= @sql_create + ' VALUES        ('''+@INSFIRSTNAME+''','''+ @INSSURNAME + ''','''+ @INSSTAFFNO+ ''',''' + @INSYEAR + ''')'

--execute the query contained inside the variable
exec sp_executesql  @sql_create

Now you can select from your table:

edited Jul 20, 2017 at 8:16

answered Jul 20, 2017 at 8:09

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

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

3 Comments

Panagiotis Kanavos Over a year ago

@Francis works but is still a very, very, very bad idea. Don't use dynamic SQL. If you need to build create a deployment script, use sqlcmd scripts instead, which do understand database and table variable names

Panagiotis Kanavos Over a year ago

@Andrea apart from the obvious issues of SQL injection and concatenation problems (whitespaces in names? Multiple surnames? SAME NAMES?), creating a different table per customer/employee and year is simply a very bad idea. You can partition a single table by the name, date fields, you can use different schemas per customer and partition by date

Andrea Over a year ago

@PanagiotisKanavos Yes, you are absolutely right: it is surely a bad design.

Jimbot · Accepted Answer · 2017-07-20 08:08:23Z

0

You could use dynamic query like this :

DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE TABLE ' + @TABLENAME + ' THE REST OF YOUR QUERY' 
Exec @sql
SET @sql = 'INSERT INTO ' + @TABLENAME + ' THE REST OF YOUR QUERY' 
Exec @sql

Not nice, but it work.

answered Jul 20, 2017 at 8:08

Jimbot

6915 silver badges24 bronze badges

Collectives™ on Stack Overflow

Create a table using a variable as the table name

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related