CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Maximus
Maximus

Reputation: 25

Loop through table of tables to dynamically CREATE table structures

I have a metadata table which has four columns Table_N, Column_N, DType & DLength, I am trying to loop through the metadata table and for each distinct table dynamiaclly spit CREATE TABLE statement with all of its columns, datatypes and datalength. Basically a typical table structure or definition.

So far I have added a cursor to loop through them and hit a roadblock with Dynamic SQL syntax expecially dealing with NULL values for certain column's values for DLength field. For example, if the data type for a column is say DATE then the Dlength value for that column would be NULL. If I concatenate a column that has VARCHAR for DType and 100 for DLength then '@DT' + '(' + @DL+ ')' would result in VARCHAR(100) but with DATE,INT or any similar Datatypes, I need a different approach. Also, not a big fan of cursor, so if you have an entirely different approach of this then please feel free to advise. Any suggestion is much appreciated. Thank you!

Table Name: DDC_Loop(Sample below with 2 tables metadata)

Table_N         Column_N    DType       DLength
--------------------------------------------------------
AUT_C_TABLOG    ORIGINAL    VARCHAR             1
AUT_C_TABLOG    PROTOCOL    VARCHAR             1
AUT_C_TABLOG    TABNAME         VARCHAR            30
ANLA             GEGST          VARCHAR             8
ANLA             GPLAB            DATE             NULL
ANLA             GRBLT          VARCHAR             5
ANLA             GRBND          VARCHAR             5
ANLA             KTOGR          VARCHAR             8
ANLA             LAND1          VARCHAR             3
ANLA             MENGE          NUMERIC           16,3

Upvotes: 0

Views: 1049

Answers (2)

Zhorov
Zhorov

Reputation: 29943

You may try to generate a dynamic statement and execute this statement. What is needed here is to number rows for each different table name, and then, when the row number is 1, to include CREATE TABLE part of SQL statement.

-- Table
CREATE TABLE #DDC_Loop (
    Table_N nvarchar(100),         
    Column_N nvarchar(100),    
    DType nvarchar(100),       
    DLength nvarchar(100)
)
INSERT INTO #DDC_Loop
    (Table_N, Column_N, DType, DLength)
VALUES
    ('AUT_C_TABLOG', 'ORIGINAL', 'VARCHAR', '1'),
    ('AUT_C_TABLOG', 'PROTOCOL', 'VARCHAR', '1'),
    ('AUT_C_TABLOG', 'TABNAME',  'VARCHAR', '30'),
    ('ANLA',         'GEGST',    'VARCHAR', '8'),
    ('ANLA',         'GPLAB',    'DATE',    NULL),
    ('ANLA',         'GRBLT',    'VARCHAR', '5'),
    ('ANLA',         'GRBND',    'VARCHAR', '5'),
    ('ANLA',         'KTOGR',    'VARCHAR', '8'),
    ('ANLA',         'LAND1',    'VARCHAR', '3'),
    ('ANLA',         'MENGE',    'NUMERIC', '16,3')

-- Dynamci statement
DECLARE @stm nvarchar(max)
SET @stm = N''
;WITH cte AS (
    SELECT 
        Table_N, 
        Column_N, 
        DType, 
        DLength,
        ROW_NUMBER() OVER (PARTITION BY Table_N ORDER BY Table_N) RN,
        CASE 
            WHEN DLength IS NULL THEN N', ' + Column_N + N' ' + DType
            ELSE N', ' + Column_N + N' ' + DType + N'(' + DLength + N')'
        END ColumnDefinition
    FROM #DDC_Loop
)
SELECT 
    @stm = @stm + 
    CASE 
        WHEN RN = 1 THEN N'); CREATE TABLE ' + Table_N + N'(' + STUFF(ColumnDefinition, 1, 2, N'')
        ELSE ColumnDefinition
    END
FROM cte
ORDER BY Table_N, RN
SET @stm = STUFF(@stm, 1, 3, N'') + SUBSTRING(@stm, 1, 3)

-- Print and execute statement
PRINT @stm
EXEC (@stm)

Generated statement:

CREATE TABLE ANLA(GEGST VARCHAR(8), GPLAB DATE, GRBLT VARCHAR(5), GRBND VARCHAR(5), KTOGR VARCHAR(8), LAND1 VARCHAR(3), MENGE NUMERIC(16,3)); 
CREATE TABLE AUT_C_TABLOG(ORIGINAL VARCHAR(1), PROTOCOL VARCHAR(1), TABNAME VARCHAR(30));

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

With a common table expression, lag, lead, a couple of case epxressions and for xml path you can return the create table statements for the entire content of the table as one long string.

First, create and populate sample table (Please save us this step in your future questions)

CREATE TABLE Tables
(
    Table_N sysname,         
    Column_N sysname,    
    DType sysname,
    DLength varchar(5)
);

INSERT INTO Tables (Table_N, Column_N, DType, DLength) VALUES
('AUT_C_TABLOG', 'ORIGINAL', 'VARCHAR', '1'),
('AUT_C_TABLOG', 'PROTOCOL', 'VARCHAR', '1'),
('AUT_C_TABLOG', 'TABNAME',  'VARCHAR', '30'),
('ANLA', 'GEGST', 'VARCHAR', '8'),
('ANLA', 'GPLAB', 'DATE', NULL),
('ANLA', 'GRBLT', 'VARCHAR', '5'),
('ANLA', 'GRBND', 'VARCHAR', '5'),
('ANLA', 'KTOGR', 'VARCHAR', '8'),
('ANLA', 'LAND1', 'VARCHAR', '3'),
('ANLA', 'MENGE', 'NUMERIC', '16,3');

Then, use a cte to get the lag and lead table name (so you would know when to add add create table and when not to):

WITH CTE AS 
(
    SELECT  Table_N, 
            Column_N, 
            DType, 
            DLength, 
            LAG(Table_N) OVER(ORDER BY Table_N) AS PrevTableName,
            LEAD(Table_N) OVER(ORDER BY Table_N) AS NextTableName
    FROM Tables
)

Now, use a few case expressions in the query:

SELECT  CASE WHEN PrevTableName IS NULL OR Table_N <> PrevTableName 
            THEN 'CREATE TABLE '+ Table_N +' (' 
            ELSE '' 
        END + Column_N +' '+ DType +
        CASE WHEN DLength IS NULL 
            THEN '' 
            ELSE '('+ DLength +')'
        END +
        CASE WHEN NextTableName IS NULL OR Table_N <> NextTableName 
            THEN '); '
            ELSE ', ' 
        END
FROM CTE
ORDER BY Table_N, Column_N -- Don't forget this order by, it's important!
FOR XML PATH('')

Results:

CREATE TABLE ANLA (GEGST VARCHAR(8), GPLAB DATE, GRBLT VARCHAR(5), GRBND VARCHAR(5), KTOGR VARCHAR(8), LAND1 VARCHAR(3), MENGE NUMERIC(16,3)); 
CREATE TABLE AUT_C_TABLOG (ORIGINAL VARCHAR(1), PROTOCOL VARCHAR(1), TABNAME VARCHAR(30));

Upvotes: 1

Related Questions