Select chunked data with row number using T-SQL

Question

I have a SQL Server table containing a long text (varchar(max)) column and several key columns. I need to load this data into another table, but break the long text into chunks (varchar(4000)), so each row in the source table may become multiple rows in the target table.

Is there a way using T-SQL which can do this in one select statement and also provide a row_number? Sort of like partition over chunk size, if this makes sense.

score 0 · Answer 1 · answered Mar 10 '19 at 23:04

You can use a recursive subquery:

with cte as (
      select left(longtext, 4000) as val, 1 as rn,
             stuff(longtext, 1, 4000, '') as rest
      from t
      union all
      select left(rest, 4000), rn + 1,
             stuff(longtext, 1, 4000, '') as rest
      from cte
      where longtext > ''
     )
select *
from cte;

You probably want to include other columns as well, but your question doesn't mention them.

Also, if your text is really long (say more than a handful of chunks per row), then this is not going to be the most efficient method. There are related methods using recursive CTEs to solve this.

score 0 · Accepted Answer · edited Mar 11 '19 at 23:26

Recursive CTE to split the string into 4000 character chunks (as a variation on this comma splitting thread: Turning a Comma Separated string into individual rows)

;WITH cte(SomeID, RowNum, DataItem, String) AS
(
    SELECT
        SomeID,
        1,
        LEFT(String, 4000),
        case when len(String) > 4000 then
            RIGHT(String, LEN(String) - 4000)
        else
            ''
        end
    FROM myTable
    UNION all

    SELECT
        SomeID,
        RowNum + 1,
        LEFT(String, 4000),
        case when len(String) > 4000 then 
            RIGHT(String, LEN(String) - 4000)
        else
            ''
        end
    FROM cte
    WHERE
        String > ''
)
SELECT *
FROM cte

Select chunked data with row number using T-SQL

2 Answers2