insert column data from one table, parse and insert into other table

  • I have a csv file that is input into a varchar(max) column in an Azure SQL Database table.  When the file is inserted into this table column I would like to take the data in this column, parse it and insert it contents into various columns of another table.

    If I were doing this in an app I would create a method that looped through the data, parsed it and added it to some sort of collection and then work with the elements within the collection.

    Is it pretty much the same in Azure SQL Database?  Would I create an after insert trigger, get the column data, loop through it, add it to a collection and then perform an insert into the other table?  Or is their a better way?

    My apologies if this is a stupid question but until very recently my SQL skills have been relegated to CRUD operations so I have a lot to learn.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • You already have a splitter function available in STRING_SPLIT().   It will parse the CSV string into a set of rows with a single column heading of [Value].

    SELECT value FROM STRING_SPLIT('Lorem,ipsum,dolor,sit,amet.', ',');

    To get the rows back into columns, you can use a cross tab.   In the code below, the ROW_NUMBER() function was used to create a ROWID column to uniquely identify the values being returned from the STRING_SPLIT() function.

    With cte as (SELECT value, RowID = ROW_NUMBER() over(order by (select null))
    FROM STRING_SPLIT('Lorem,ipsum,dolor,sit,amet.', ','))

    select Column1 = max(case when RowID = 1 then value end)
    ,Column2 = max(case when RowID = 2 then value end)
    ,Column3 = max(case when RowID = 3 then value end)
    ,Column4 = max(case when RowID = 4 then value end)
    ,Column5 = max(case when RowID = 5 then value end)
    from cte

    Hope this helps.

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • jim0211 wrote:

    I have a csv file that is input into a varchar(max) column in an Azure SQL Database table.  When the file is inserted into this table column I would like to take the data in this column, parse it and insert it contents into various columns of another table.

    Does the CSV File you speak of have a consistent row format?  If so, then instead of inserting it into a VARCHAR(MAX) as a single blob, why not use BULK INSERT to import it into a table where each row is actually a row and there are columns in the table according to the record layout?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 4 posts - 1 through 4 (of 4 total)

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