How to create tables using another table’s columns in SQL Server? [closed]

I have a table that contains multiple table’s information. I want to create more tables based on that table’s data. col_1 has the table names, col_2 contains column names and col_3 defines the data types.

I tried with using a cursor, but it’s not working. Can anyone help with this task?

col_1    col_2           col_3
-----------------------------------    
table_A  COLUMN_1   VARCHAR(20)
table_A  COLUMN_2   INT
table_A  COLUMN_3   VARCHAR(20)
table_B  COLUMN_1   INT
table_B  COLUMN_2   VARCHAR(20)
table_B  COLUMN_3   VARCHAR(20)
table_C  COLUMN_1   VARCHAR(20)
table_C  COLUMN_2   VARCHAR(20)
table_C  COLUMN_3   VARCHAR(20)
table_D  COLUMN_1   VARCHAR(20)

  • 1

    Please show what you tried

    – 

  • 2

    “but it’s not working” What does that mean? You got an error? What was the error? And what code were you trying to use?

    – 

Assuming the table is called TableInformation what you could do something like this:

--First we need to create a table to store the SQL Commands to create the tables
DECLARE @Commands AS TABLE 
  (Command VARCHAR(MAX))

 -- Then we need to create the Commands by grouping up the col_1 table name values and getting the column names and types
  INSERT INTO @Commands(Command)
  SELECT 'CREATE TABLE '+ TableName+'('+ ColumnInfo+')'
  FROM
  (SELECT ti2.col_1 AS TableName,STUFF((SELECT DISTINCT ',' + col_2 +' '+col_3
          FROM TableInformation ti
          WHERE ti.col_1 = ti2.col_1
          FOR XML PATH (''))
          , 1, 1, '') AS ColumnInfo
  FROM TableInformation ti2
  GROUP BY col_1) tabl

--After we get the necessary information we need to create a cursor to loop through our commands
DECLARE @CommandToExecute VARCHAR(MAX)

DECLARE CommandCursor CURSOR FOR
SELECT Command
FROM @Commands

OPEN CommandCursor
FETCH NEXT FROM CommandCursor INTO @CommandToExecute

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Execute the Command thus creating the table
    EXEC(@CommandToExecute)

    FETCH NEXT FROM CommandCursor INTO @CommandToExecute
END

CLOSE CommandCursor
DEALLOCATE CommandCursor

Leave a Comment