Insert from string only inserting first row

Question

We receive a set of files monthly that are just text. They have no line breaks or delimiters.

I am trying to insert substrings into multiple columns and multiple rows from a table with a large single string column. But only the first row gets inserted...

CREATE TABLE [dbo].[claims_stage]
(
     [stage] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Insert from flat file to claims_stage:

bulk insert CLAIMS_STAGE from '\filepath\filename'

Insert split strings into new table from CLAIMS_STAGE:

insert into
CCLF8
(
    BENE_MBI_ID,
    BENE_HIC_NUM,
    BENE_FIPS_STATE_CD,
    BENE_FIPS_CNTY_CD,
    BENE_ZIP_CD,
    BENE_DOB,
    BENE_SEX_CD,
    BENE_RACE_CD,
    BENE_AGE,
    BENE_MDCR_STUS_CD,
    BENE_DUAL_STUS_CD,
    BENE_DEATH_DT,
    BENE_RNG_BGN_DT,
    BENE_RNG_END_DT,
    BENE_1ST_NAME,
    BENE_MIDL_NAME,
    BENE_LAST_NAME,
    BENE_ORGNL_ENTLMT_RSN_CD,
    BENE_ENTLMT_BUYIN_IND,
    BENE_PART_A_ENRLMT_BGN_DT,
    BENE_PART_B_ENRLMT_BGN_DT,
    BENE_LINE_1_ADR,
    BENE_LINE_2_ADR,
    BENE_LINE_3_ADR,
    BENE_LINE_4_ADR,
    BENE_LINE_5_ADR,
    BENE_LINE_6_ADR,
    GEO_ZIP_PLC_NAME,
    GEO_USPS_STATE_CD,
    GEO_ZIP5_CD,
    GEO_ZIP4_CD, 
    [INSERT_DT],
    [FILE_NAME]
)
select
    substring(stage, 1, 11)     as 'BENE_MBI_ID',
    substring(stage, 12, 11)    as 'BENE_HIC_NUM',
    substring(stage, 23, 2)     as 'BENE_FIPS_STATE_CD',
    substring(stage, 25, 3)     as 'BENE_FIPS_CNTY_CD',
    substring(stage, 28, 5)     as 'BENE_ZIP_CD',
    substring(stage, 33, 10)    as 'BENE_DOB',
    substring(stage, 43, 1)     as 'BENE_SEX_CD',
    substring(stage, 44, 1)     as 'BENE_RACE_CD',
    substring(stage, 45, 3)     as 'BENE_AGE',
    substring(stage, 48, 2)     as 'BENE_MDCR_STUS_CD',
    substring(stage, 50, 2)     as 'BENE_DUAL_STUS_CD',
    substring(stage, 52, 10)    as 'BENE_DEATH_DT',
    substring(stage, 62, 10)    as 'BENE_RNG_BGN_DT',
    substring(stage, 72, 10)    as 'BENE_RNG_END_DT',
    substring(stage, 82, 30)    as 'BENE_1ST_NAME',
    substring(stage, 112, 15)   as 'BENE_MIDLNAME',
    substring(stage, 127, 40)   as 'BENE_LAST_NAME',
    substring(stage, 167, 1)    as 'BENE_ORGNL_ENTLMT_RSN_CD',
    substring(stage, 168, 1)    as 'BENE_ENTLMT_BUYIN_IND',
    substring(stage, 169, 10)   as 'BENE_PART_A_ENRLMT_BGN_DT',
    substring(stage, 179, 10)   as 'BENE_PART_B_ENRLMT_BGN_DT',
    substring(stage, 189, 45)   as 'BENE_LINE_1_ADR',
    substring(stage, 234, 45)   as 'BENE_LINE_2_ADR',
    substring(stage, 279, 40)   as 'BENE_LINE_3_ADR',
    substring(stage, 319, 40)   as 'BENE_LINE_4_ADR',
    substring(stage, 359, 40)   as 'BENE_LINE_5_ADR',
    substring(stage, 399, 40)   as 'BENE_LINE_6_ADR',
    substring(stage, 439, 100)  as 'GEO_ZIP_PLC_NAME',
    substring(stage, 539, 2)    as 'GEO_USPS_STATE_CD',
    substring(stage, 541, 5)    as 'GEO_ZIP5_CD',
    substring(stage, 546, 4)    as 'GEO_ZIP4_CD',
    GETDATE()                   as 'INSERT_DT',
    'filename'                  as 'FILE_NAME'
from 
    CLAIMS_STAGE

Should I loop, how do I loop?

user_15332650 · Answer

Here's one way of parsing your data. It requires a digits/tally table - this is a permanent table with a single int column starting at 1 going up to a bazillion or whatever you need.

Here I'm just creating it on the fly for the example:

/* Sample data, 3 "rows" of fixed-width data as a single row in table */

create table claims_stage (stage varchar(max))
insert into claims_stage
select Cast('  123' as char(5)) + cast('Homer' as Char(10)) + Cast('Simpson' as char(10)) + Cast('  456' as char(5)) + 
       Cast('   56' as char(5)) + cast('Bart' as Char(10)) + Cast('Simpson' as char(10)) + Cast('  888' as char(5)) +
       Cast('  122' as char(5)) + cast('Stuey' as Char(10)) + Cast('Griffin' as char(10)) + Cast('  60' as char(5))


with d as (select * from (values (0),(1),(2))n(n)), /* Tally table */
CCLF8 as (
  select Substring(stage, 1 + d.n * 30, 30) stage           /* Knowing the length of each row, */
  from claims_stage s                                 /* break it into multiple rows */
  cross join d
)

And now select our columns from it

select 
    Substring(stage,1,5) Col1,
    Substring(stage,6,10) Col2,
    Substring(stage,16,10) Col3,
    Substring(stage,25,5) Col4
from CCLF8

Insert from string only inserting first row

1 Answers