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:

Select rows with a given identifier. (say Item1)
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.)
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:

Build a table dynamically with the ItemClass name as the table name. (so, 2 tables for Item1 here).
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

Your sql fiddle doesn't have any xml. It just has the string "xml". We can't help you build code from that to create your tables. And it is built in mysql, not sql server. — Sean Lange
– Sean Lange, Commented Oct 11, 2017 at 16:49
@SeanLange thanks for checking out my question. Yes, I kind of said that we can assume that I do extract a table from the xml. so, instead of xml: consider any table : plz see this: sqlfiddle.com/#!9/6de0da/1/0 : I have to get column names and rows from xml table => and the table name from the class name. I could not find any place to demo sql server. Is there sql server fiddle kind of thing? — Rajesh Ronaldo
– Rajesh Ronaldo, Commented Oct 11, 2017 at 16:55
So you only need help to create the tables? In your question you state that you need that part. Here is the problem. You state you know how to get the columns but you can't create the script to create the table. We can't help you here because the table AND the columns need to be in the same statement. — Sean Lange
– Sean Lange, Commented Oct 11, 2017 at 17:00
@SeanLange no, i say that i do have the table created from the xml value for each row. The columns & rows of the table that i derived from the xml (Mint the fiddle linked above) will become the columns and rows of the newly created table with name = ItemClass. ok....well is there a way to create a table with name and columns passed as variables i.e. i will get them at runtime? If that can be done, surely the question can be solved. — Rajesh Ronaldo
– Rajesh Ronaldo, Commented Oct 11, 2017 at 17:08
Not with variables no. You will have to create dynamic sql and execute it. Surely if you could be troubled to post all the information I could show you instead of guessing what you are struggling with. — Sean Lange
– Sean Lange, Commented Oct 11, 2017 at 17:14

gofr1 · Accepted Answer · 2017-10-12 10:24:42Z

@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.

Him · Accepted Answer · 2017-10-12 07:14:38Z

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

Alex · Accepted Answer · 2017-10-11 16:51:44Z

0

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.

answered Oct 11, 2017 at 16:51

Alex

7411 gold badge6 silver badges22 bronze badges

2 Comments

Rajesh Ronaldo Over a year ago

thanks for your help, I am a bit naive in sql: could you explain using the example in the question? I can try and build upon it using the example code.

Alex Over a year ago

Well, here is how you create a cursor, which should select everything from your table and for each row perform 2 actions: create table and insert rows. Do you have this xml parsed already? If yes, than first query should be: execute('create table ' + ItemClass + '(' + %list of column% + ')') - where list of columns you get from your xml column. Similar way you form insert query.

Collectives™ on Stack Overflow

SQL: Dynamically Build a Table from Another Table

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related