SQL: Dynamically Build a Table from Another Table

Question

My question is a bit unusual.

I have a table with 3 columns (SQL Fiddle Link):

ItemIdentifer  ItemClasses            RelevantItemDetails

Item1          Software              <xml document>
Item1          CRM                   <xml document>

Now, what I need to do is:

1. Select rows with a given identifier. (say Item1)

2. Build a temporary table for each of the rows with the given identifier: such that Table_Name is the ItemClass of that row. (so, in this case, I will have 2 tables -- Software and CRM.)

3. Convert the RelevantItemDetails value => an xml text => into the table entries of the corresponding tables (I know this :)).

I have learnt to do step 3 through openxml page https://learn.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql . I created a sql procedure which returns a table from the xml. Now, I just have to insert it into the temporary table I created in step 2 (with both column names and values). And I am lost beyond this.

I suspect that for 1 & 2, I have to build a procedure that does these things dynamically.

Any help will be greatly helpful.

To clarify: I am not getting 2 things:

1. Build a table dynamically with the ItemClass name as the table name. (so, 2 tables for Item1 here).

2. The columns and rows of that table are defined by the xml document (i.e. another table).

So, I just need to create tables with name defined by ItemClass and columns and row entries defined by the xml document (assume I have a table created from the xml, it too will be dynamically generated, but I know how to generate it).

I read creating tables dynamically here (T-SQL How to create tables dynamically in stored procedures?) but then the columns are fixed. In my case columns are derived from another table. And I have to insert values too (again, derived from the same table from which columns come).

EDIT: Sample row.

ID  ItemName    ItemClass   Details
10   WebApp     Software   <root><row ID="10" ItemName="WebApp" ItemDescription="desc" DisplayID="4962" /></root>

EDIT: Sample Final Table.

TableName == Software

ID  ItemName  ItemDescription  DisplayID 
10  WebApp    "desc"            4962

Answer 1

The below code snippets should help you achieve the objective .

            -- Lets build the table     
            IF OBJECT_ID(N'dbo.Rajesh', N'U') IS  NULL  
             BEGIN  
                            CREATE TABLE Rajesh 
                            (                
                             ItemIdentifer varchar(100),
                             ItemClasses varchar(100)
                            )
            END 
             -- Insert the data 
                        insert into Rajesh values ('Item1','Software')
                        insert into Rajesh values ('Item1','CRM')
            --  Since we need to to loop all the rows and I was not sure if the intitial table has any identity column , we need the indentity column as we will use that
            -- while looping . You can always use cusrsor , i have used this .
            IF OBJECT_ID(N'dbo.Temp', N'U') IS  NULL    
             BEGIN  
                            CREATE TABLE Temp 
                            (   
                             id int identity(1,1),           
                             ItemIdentifer varchar(100),
                             ItemClasses varchar(100)
                            )
            END
                        INSERT INTO Temp (ItemIdentifer,ItemClasses) SELECT ItemIdentifer,ItemClasses FROM Rajesh
                        DECLARE @MAXiD INT 
                        DECLARE @iD INT 
                        SELECT @ID = 1
                        DECLARE @COMMANDSTRING VARCHAR(200)
                        DECLARE  @ItemClasses VARCHAR(200)
                        SELECT @MAXiD =MAX(ID) FROM TEMP

                        WHILE (@Id <= @MAXiD)  
                            BEGIN  
                                SELECT @ItemClasses =  'DBO.'+ItemClasses FROM TEMP WHERE ID = @ID      
                                -- As I am unaware of the table staructire for the itemclasses and just took something , please update this accordingly            
                                SELECT @COMMANDSTRING = ' IF OBJECT_ID(N'''+@ItemClasses+''', N''U'') IS  NULL BEGIN  CREATE TABLE '+@ItemClasses+' (    ItemIdentifer varchar(100),ItemClasses varchar(100)) END '             
                                EXEC (@COMMANDSTRING)               
                                SELECT @Id = @Id +1 
                            END

Answer 2

@items table is analogy to your current table. Then there are steps to generate scripts for each ItemClass.

DECLARE @n int,  -- counter
        @i int = 0, -- counter
        @tableName nvarchar(255), -- stores name of the current table
        @query nvarchar(4000)  -- stores a query that creates table

-- creates table like yours
DECLARE @items TABLE (
    ID int,
    ItemName nvarchar(100),
    ItemClass nvarchar(100),
    Details xml
)
-- put some data in a table
INSERT INTO @items VALUES
(10, N'WebApp', N'Software', N'<root><row ID="10" ItemName="WebApp" ItemDescription="desc" DisplayID="4962" /></root>'),
(12, N'WebApp', N'Software', N'<root><row ID="12" ItemName="WebApp" ItemDescription="desc" DisplayID="5687" /></root>'),
(11, N'CRMapp', N'CRM', N'<root><row ID="11" ItemName="CRMapp" ItemDescription="desc" DisplayID="823678" /></root>')

-- that table will store data for script generating
DECLARE @tables TABLE (
    ID int,
    TableName nvarchar(100),
    ColumnName sysname,
    ColumnValue nvarchar(max)
)
--here we parse XML to get column names and data
INSERT INTO @tables
SELECT  i.ID,
        i.ItemClass as TableName,
        CAST(t.c.query('local-name(.)') AS nvarchar(255)) AS ColumnName,
        t.c.value('.', 'nvarchar(max)') ColumnValue
FROM @items i
CROSS APPLY Details.nodes('//@*') as t(c)
-- count tables
SELECT @n = COUNT(DISTINCT TableName)
FROM @tables

-- here we go!
WHILE @n > @i
BEGIN
    -- select some table 
    SELECT TOP 1 @tableName = TableName
    FROM @tables
    -- in this CTE we get column names in order
    ;WITH tables_ AS (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID ) as rn,
                ColumnName
        FROM @tables
        WHERE TableName = @tableName    
    )


    -- here we build the create part
    SELECT @query = N'CREATE TABLE ' + QUOTENAME(@tableName) + '('
    +
    STUFF((
        SELECT N','+ QUOTENAME(ColumnName) +' nvarchar(max)'
        FROM tables_
        GROUP BY rn,ColumnName
        ORDER BY rn
        FOR XML PATH('')),1,1,'')
    +');
INSERT INTO ' + QUOTENAME(@tableName) +' VALUES'
    -- here comes data
    ;WITH cte AS (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID ) as rn,
                ID,
                ColumnValue
        FROM @tables
        WHERE TableName = @tableName
    )

    SELECT @query = @query + STUFF((
    SELECT ',('''+ [1] + ''',''' +[2] + ''',''' +[3] + ''',''' +[4]+''')'
    FROM cte
    PIVOT (max (ColumnValue) for rn in ([1],[2],[3],[4])) as bb
    FOR XML PATH('')),1,1,'');
    -- print query
    PRINT @query
    ---- execute query (first runt this script to print!)
    --EXEC (@query)

    SET @i+=1
    SET @query = N''

    DELETE FROM @tables
    WHERE TableName = @tableName
END

That will generate (and execute) scripts like:

CREATE TABLE [Software]([ID] nvarchar(max),[ItemName] nvarchar(max),[ItemDescription] nvarchar(max),[DisplayID] nvarchar(max));
INSERT INTO [Software] VALUES('10','WebApp','desc','4962'),('12','WebApp','desc','5687')

CREATE TABLE [CRM]([ID] nvarchar(max),[ItemName] nvarchar(max),[ItemDescription] nvarchar(max),[DisplayID] nvarchar(max));
INSERT INTO [CRM] VALUES('11','CRMapp','desc','823678')

You can add checking if table already exists. And some logic to obtain right datatypes for table columns. And maybe there can be more than 4 attributes in XML.

Answer 3

You can use EXECUTE(@script) function and pass any kind of script into it as string. Like:

EXECUTE('Create table my_table(id int)')

So just iterate through query and pass whatever scripts you need into EXECUTE.