Is it possible to create tables Dynamically using Cursors from another table with csv values

  • Have a Table with the CSV Values in the columns as below

    ID Name text

    1 SID,DOB 123,12/01/1990

    2 City,State,Zip NewYork,NewYork,01234

    3 SID,DOB 456,12/21/1990

    What is need to get is 2 tables in this scenario as out put with the corresponding values

    ID SID DOB

    1 123 12/01/1990

    3 456 12/21/1990

    ID City State Zip

    2 NewYork NewYork 01234

    Is there any way of achieving it using a Cursor or any other method in SQL server?

  • I'm not sure how exactly the source table and the data is formatted. Can you please provide a runnable example that creates the table and inserts some sample rows that will cover your test case? Something like in this article[/url]. ๐Ÿ™‚

  • I'm afraid I'm just not sure what you're after from the post above. If you post a create table statement and some inserts to populate it, then describe what you want the result to be, we may be able to help you out.

  • Hi,

    Yes please see the Create table script for source table and the insert script as well

    CREATE TABLE yt

    ([ID] int, [Name] varchar(14), [text] varchar(21))

    ;

    INSERT INTO yt

    ([ID], [Name], [text])

    VALUES

    (1, 'SID,DOB', '123,12/01/1990'),

    (2, 'City,State,Zip', 'NewYork,NewYork,01234'),

    (3, 'SID,DOB', '456,12/21/1990')

    ;

    Now the out put should have 2 tables as i mentione in my question. and these values in the source table are getting populated dynamically so i cannot hardcode them in my script for generating the output tables.

  • sravan.here (4/18/2013)


    Hi,

    Yes please see the Create table script for source table and the insert script as well

    CREATE TABLE yt

    ([ID] int, [Name] varchar(14), [text] varchar(21))

    ;

    INSERT INTO yt

    ([ID], [Name], [text])

    VALUES

    (1, 'SID,DOB', '123,12/01/1990'),

    (2, 'City,State,Zip', 'NewYork,NewYork,01234'),

    (3, 'SID,DOB', '456,12/21/1990')

    ;

    Now the out put should have 2 tables as i mentione in my question. and these values in the source table are getting populated dynamically so i cannot hardcode them in my script for generating the output tables.

    This is an unusual transport file with metadata (partial, column names only) and data as string on the same row. Where / how was it generated? How does it deal with embedded quotes?

    There are two row types in the sample, one with metadata defining two columns 'SID,DOB' and the other defining three columns 'City,State,Zip'. The key question is - are they constant throughout the table? Are there any rows having 'Street,City,State,Zip' as the metadata?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Yes there are tables with 4 or 5 or more columns as well as you doubted.

    I have just provide a simple example here.

    But the maximum column names (with csv) will be 26 in the meta file / table.

    I am just looking for a way to create tables on the fly.

  • sravan.here (4/19/2013)


    Yes there are tables with 4 or 5 or more columns as well as you doubted.

    I have just provide a simple example here.

    But the maximum column names (with csv) will be 26 in the meta file / table.

    I am just looking for a way to create tables on the fly.

    How do you identify which rows go into which tables? With your sample data it's easy - two tables. A larger and more realistic sample data set would be very useful.

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • All the similar rows should go into one table. In my example, row 1 and 3 will be in table and 2 will be another table. but my metatable will have thousands of rows and possibly they have 10 or 20 tables that will have to be created.

  • sravan.here (4/19/2013)


    All the similar rows should go into one table. In my example, row 1 and 3 will be in table and 2 will be another table. but my metatable will have thousands of rows and possibly they have 10 or 20 tables that will have to be created.

    This is straightforward to do, coding against the simple sample dataset:

    SET NOCOUNT ON

    ------------------------------------------------------------------

    -- get the list of tables and their column list

    ------------------------------------------------------------------

    IF object_id('tempdb..#TableList') IS NOT NULL

    DROP TABLE #TableList

    SELECT DISTINCT

    TableCount = MAX(TableID) OVER(PARTITION BY 1),

    TableID,

    TableName = 'Staging.dbo.Table' + RIGHT('00'+CAST(TableID AS VARCHAR(3)),3),

    ColumnNumber,

    ColumnName

    INTO #TableList

    FROM (

    SELECT

    TableID = DENSE_RANK() OVER(ORDER BY yt.[Name]),

    ColumnNumber = x.ItemNumber,

    ColumnName = x.Item

    FROM yt

    CROSS APPLY dbo.DelimitedSplit8K(yt.[Name],',') x

    ) d

    ORDER BY TableID, ColumnNumber

    ------------------------------------------------------------------

    -- shape the data for convenience

    ------------------------------------------------------------------

    IF object_id('tempdb..#ProcessedData') IS NOT NULL

    DROP TABLE #ProcessedData

    ;WITH ProcessedData AS (

    SELECT

    TableID = DENSE_RANK() OVER(ORDER BY [Name]),

    RowID = ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY ID),

    [Name],

    [Text]

    FROM yt

    )

    SELECT

    TableID,

    RowID,

    x.ItemNumber,

    ColumnName = x.Item,

    ColumnValue = y.Item

    INTO #ProcessedData

    FROM ProcessedData yt

    CROSS APPLY dbo.DelimitedSplit8K([Name],',') x

    CROSS APPLY dbo.DelimitedSplit8K([Text],',') y

    WHERE y.ItemNumber = x.ItemNumber

    ORDER BY TableID, RowID

    ------------------------------------------------------------------

    -- Cycle through the list of tables, create or truncate

    -- Then load with data

    ------------------------------------------------------------------

    -- set up some variables

    DECLARE @TableID INT, @ColumnList VARCHAR(1000), @TableName VARCHAR(100), @Script VARCHAR(2000)

    SET @TableID = 1

    WHILE @TableID > 0

    BEGIN

    -- create or truncate the table

    SELECT @TableName = TableName

    FROM #TableList

    WHERE TableID = @TableID

    IF @@ROWCOUNT = 0 BREAK

    SET @ColumnList = NULL

    SELECT @ColumnList = ISNULL(@ColumnList + ', ',' ') + ColumnName + ' VARCHAR(100)'

    FROM #TableList

    WHERE TableID = @TableID

    ORDER BY ColumnNumber

    SET @Script =

    'IF object_id(''' + @TableName + ''') IS NULL' + CHAR(10) +

    'CREATE TABLE ' + @TableName + ' (' + @ColumnList + ') ' + CHAR(10) +

    'ELSE ' + CHAR(10) +

    'TRUNCATE TABLE ' + @TableName

    PRINT ''

    PRINT @Script

    EXEC(@Script)

    -- insert data into table @TableName

    SET @ColumnList = NULL

    SELECT @ColumnList = ISNULL(@ColumnList + ', ', ' ') +

    '[' + ColumnName + '] = MAX(CASE WHEN ItemNumber = ' + CAST(ColumnNumber AS VARCHAR(3)) +

    ' THEN ColumnValue END)' + CHAR(10)

    FROM #TableList

    WHERE TableID = @TableID

    ORDER BY ColumnNumber

    SET @Script =

    'INSERT INTO ' + @TableName + CHAR(10) +

    'SELECT ' + CHAR(10) + @ColumnList +

    'FROM #ProcessedData ' + CHAR(10) +

    'WHERE TableID = ' + CAST(@TableID AS VARCHAR(3)) + CHAR(10) +

    'GROUP BY RowID'

    PRINT ''

    PRINT @Script

    EXEC(@Script)

    PRINT ''

    PRINT '-----------------------------------------------------------------'

    SET @TableID = @TableID + 1

    END

    SELECT * FROM Staging.dbo.Table001

    SELECT * FROM Staging.dbo.Table002

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (4/19/2013)


    This is an unusual transport file with metadata (partial, column names only) and data as string on the same row.

    BWAAAA-HAAA!!!!! I say the same thing everytime I have to use XML. ๐Ÿ˜›

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/22/2013)


    ChrisM@Work (4/19/2013)


    This is an unusual transport file with metadata (partial, column names only) and data as string on the same row.

    BWAAAA-HAAA!!!!! I say the same thing everytime I have to use XML. ๐Ÿ˜›

    XML is worse ! This thing only agonisingly repeats the column name once per row ๐Ÿ˜›

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply