Split a single row to multiple rows based on the string position

We have a requirement that we need to split a row into multiple rows and store it into the database. Basically we are getting a CSV file with a single row we need to break this single row to multiple rows each rows having 16 characters.

 For example

Row in CSV file

Asdfghjkqwertyuiqwertyuiopasdfghqwert12345678901bageshf4567123456574536245718234

We need to load split this row into the multiple rows having 16 characters. Last rows may have variable length.

Records in the table.

Asdfghjkqwertyui

Qwertyuiopasdfgh

qwert12345678901

bageshf456712345

6574536245718234

Let’s see how we achieve this.

First we will load the CSV file into the staging table after that we will write the SQL Script to split the row into multiple rows based on the string position.

We are using SUBSTRING function in SQL Script.

Read more about the SUBSTRING function in SQL server here :

https://bageshkumarbagi-msbi.blogspot.com/2016/06/replace-stuff-and-sub-string-function.html

Below is the sating and main table.

Stage table

create table CR_Info_STG ( CR_Row_Id int not null identity(1,1),  CR_Row_value varchar(max) )

Main table

create table CR_Info ( CR_Row_Id int not null identity(1,1),  CR_Row_value varchar(max) )

Table created successfully.

Below is the CSV file. 

We are loading this file into stage table. 

Running this package. 

Data in Stage table. 

Now we are taking execute sql task to split the row into multiple rows.

DECLARE @str          VARCHAR(max),         @strlen       INT,         @i            INT,         @numberofchar INT,         @rowcount     INT,         @curr_row     INT;  SET @curr_row=1; SET @rowcount =(SELECT Count(*)                 FROM   cr_info_stg);  WHILE @curr_row <= @rowcount   BEGIN       SET @i=1;       SET @numberofchar=16;       SET @str=(SELECT cr_row_value                 FROM   cr_info_stg                 WHERE  cr_row_id = @curr_row);       SET @strlen=Len(@str);        WHILE @i < @strlen         BEGIN             INSERT INTO cr_info                         (cr_row_value)             SELECT Substring(@str, @i, @numberofchar)              SET @i=@i + @numberofchar;         END;        SET @curr_row=@curr_row + 1;   END;

 Adding the execute SQL task.  

Package is ready to run.   

Running the package. Before running the package values in the table. 

Running the package.

Package executed successfully.  

Data loaded successfully.