How do I create tables dynamically in SQL Server?

Question

I am trying to create tables dynamically using the information in another table.

For example there is a table (say table1) which has information about the list of tables to be created. I would like to use this table1 to dynamically create new tables using the schema St. and including _New def in the end of the name i.e. I would like to create a table 'St.TableA_New' instead of the table name 'TableA' in table1. Here is the code I used.

declare @table1 table(idx int identity(1,1), table_name varchar(50))
insert into @table1 (table_name)
select'TableA'  union
select'TableB' union
select'TableC' 

DECLARE @COUNT INT = 1;
WHILE @COUNT <= (select count(*) from @table1)
BEGIN
  Declare @table_name varchar(200) = (select table_name from @table1 where idx=@COUNT);
  Declare @new_table varchar(50) = 'St.+'@table_name+'_New';
  IF OBJECT_ID(@new_table) IS NOT NULL 
   DROP TABLE @new_table;

CREATE TABLE @new_table
WITH   
(   
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
)  
AS 
    SELECT * 
    FROM [Ext].[@table_name]
    OPTION (LABEL = '');
SET @COUNT = @COUNT + 1
END;

The error says 'incorrect syntax near '@newtable.' Expecting '.',ID,IF,or QUOTED_ID' at the 'DROP TABLE @new_table;' line. What should I do to create all the tables dynamically using the names from 'table1' table?

Parameters are **value** (that is the keyword here) placeholders and can't be used as object names or type names. If you want dynamic tables you will need to dynamically construct a sql statement using string concatenation and then execute it using `EXEC(@statementHere)`. — Igor, Feb 08 '19 at 20:15
You need to use dynamic sql and properly quote your object names (probably by using `QUOTENAME`). — Larnu, Feb 08 '19 at 20:16
Oversimplified example: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a0ea163645dd8145a20bd7d33f9facbd). — Larnu, Feb 08 '19 at 20:23

score 0 · Accepted Answer · answered Feb 08 '19 at 20:21

You can use sp_executesql to do this. Just undo the comment below. You may also need to add code for the schema name.

declare @table1 table(idx int identity(1,1), table_name varchar(50))
insert into @table1 (table_name) values ('TableA'), ('TableB'), ('TableC');

Declare @table_name varchar(200)
    , @new_table varchar(50)
    , @sql nvarchar(1000);

DECLARE @COUNT INT = 1;
WHILE @COUNT <= (select count(*) from @table1)
BEGIN
    select @table_name = table_name from @table1 where idx=@COUNT;
    set @new_table = @table_name + '_New';

    set @sql = concat('drop table if exists ', quotename(@new_table), ';');
    set @sql = @sql + 'CREATE TABLE ' + quotename(@new_table) + ' WITH(DISTRIBUTION = ROUND_ROBIN, HEAP) AS SELECT * FROM ' + quotename(@table_name) + ' OPTION (LABEL = '''')';

    print @sql;
    --exec sys.sp_executesql @sql;

    SET @COUNT = @COUNT + 1
END;

How do I create tables dynamically in SQL Server?

1 Answers1