CODEWITHSUNDEEP
sql-servert-sql
alexithymia
alexithymia

Reputation: 357

How to create duplicate tables with 'similar' names of a column of a table?

I have a table that contains the names of tables. The structure is the following:

table_name  
dbo.2017_10_8   
dbo.2017_10_19
dbo.2017_10_30
dbo.2017_11_10
dbo.2017_11_21
dbo.2017_12_2
dbo.2017_12_13
dbo.2017_12_24
dbo.2018_1_4
dbo.2018_1_15
dbo.2018_1_26
dbo.2018_2_6
dbo.2018_2_17
dbo.2018_3_11
dbo.2018_2_28

I would like to create tables that have name of table like 

dbo.2017_10_8_cs
dbo.2017_10_19_cs
dbo.2017_10_30_cs
dbo.2017_11_10_cs
dbo.2017_11_21_cs
dbo.2017_12_2_cs
dbo.2017_12_13_cs
dbo.2017_12_24_cs
dbo.2018_1_4_cs
dbo.2018_1_15_cs
dbo.2018_1_26_cs
dbo.2018_2_6_cs
dbo.2018_2_17_cs
dbo.2018_3_11_cs
dbo.2018_2_28_cs

I know that if for example t contains the first block of values. I can just 

select table_name + '_cs' from t

and then I have the names in the format I want, but how can I create all tables with the name of the table having the second block of values? Thank you in advance

Upvotes: 1

Views: 162

Answers (1)

EzLo
EzLo

Reputation: 14209

You can use a CURSOR to cycle each table.

DECLARE @SourceTableName VARCHAR(100)
DECLARE @DestinationTableName VARCHAR(100)

DECLARE TableNameCursor CURSOR FOR
    SELECT
        SouceTableName = QUOTENAME(table_name),
        DestinationTableName = QUOTENAME(table_name + '_cs')
    FROM
        t

OPEN TableNameCursor
FETCH NEXT FROM TableNameCursor INTO @SourceTableName, @DestinationTableName

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQLCreate VARCHAR(MAX) = 'SELECT * INTO ' + @DestinationTableName + ' FROM ' + @SourceTableName

    DECLARE @DynamicSQLDrop VARCHAR(MAX) = 'DROP TABLE ' + @SourceTableName

    EXEC (@DynamicSQLCreate)

    EXEC (@DynamicSQLDrop)

    FETCH NEXT FROM TableNameCursor INTO @SourceTableName, @DestinationTableName

END

CLOSE TableNameCursor
DEALLOCATE TableNameCursor

Please note that I used SELECT INTO to create your new tables. This command will copy the source table structure and load it with all it's data, but not it's indexes or constraints.

You can switch the EXEC for a PRINT to validate the script before actually executing.

EDIT:

You can use the system stored procedure sp_rename to change the new table to the original one (once dropped). Put this inside the while loop:

DECLARE @DynamicSQLCreate VARCHAR(MAX) = 'SELECT * INTO ' + @DestinationTableName + ' FROM ' + @SourceTableName
DECLARE @DynamicSQLDrop VARCHAR(MAX) = 'DROP TABLE ' + @SourceTableName
DECLARE @DynamicSQLRename VARCHAR(MAX) = 'EXEC sp_rename ' + @DestinationTableName + ' , ' + @SourceTableName

EXEC (@DynamicSQLCreate)
EXEC (@DynamicSQLDrop)
EXEC (@DynamicSQLRename)

Upvotes: 2

Related Questions