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

Question

Post reply

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

jim0211

Valued Member

Points: 62
More actions
March 31, 2021 at 4:30 pm

#3864078

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.

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

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.

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