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

Question

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 its contents into various columns of another table.

Would I create an after insert trigger, get the column data, loop through it and add the elements to a list, array or some other collection (if SQL has such a thing) and then perform an insert into the other table using the collection? 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.

Answer 1

The question is not stupid, but the operation is. :-)

If you can find a way to crack the string outside the database, it will be a lot easier. For instance, in Perl you can easliy say:

my ($thatvalue, $thisvalue, $onemorevalue, ...) = split(/,/, $csvrow);

And you can do similar in Python, Powershell, you name it. Or you can use BCP to load the file, if it has a consistent format.

But once you have the long string in a single column in a table, it is not that fun any more, because T-SQL does not have an operation like the one above. Well, you can do string_split, or use a custom-written string-splitter, but they return tables, which you would need to pivot into column, and it all gets really messy.

I have an article of splitting strings into tables here: http://www.sommarskog.se/arrays-in-sql.html. But for you that is only half the story.

Answer 2

You may insert data into the staging table first from the csv file and then you can run the scripts to insert / merge data from the staging table to the destination table and also you can parse the data and insert its contents into various columns of another table.