declare @idx varchar(20)
 declare @idx1 varchar(8000)
    declare @slice nvarchar(4000)
Declare @abc varchar(255)
declare @abc1 varchar(20) = 1
declare @abc2 Int
 truncate table [crm].[dr_transactions_updated]    -----deleted all the data
SET @abc2 = (select count(Serial_number) from [crm].[stg_dr_transactions])
While @abc1<=@abc2
   begin
set @idx = (SELECT Serial_number1 FROM (
SELECT row_number() over (order by Serial_number) as r,charindex('|',Serial_number) as Serial_number1 from
  [crm].[stg_dr_transactions]) as a where r=@abc1)

   set @idx1 = (SELECT Serial_number FROM (
SELECT row_number() over (order by Serial_number) as r,charindex('|',Serial_number) as Serial_number1,REPLACE(Serial_number,'(','') AS Serial_number from
  [crm].[stg_dr_transactions]) as a where r=@abc1)
 -- print @idx
 -- print @idx1

   if @idx='0' -- To insert NA values
   BEGIN
  -- DECLARE @abc4 varchar(4000)
   INSERT into [crm].[dr_transactions_updated] ([r], [Order_Number], [Product_ID], [SKU], [Sales_Key], [Reporting_Date], [z])
    SELECT [r], [Order_Number], [Product_ID], [SKU], [Sales_Key], [Reporting_Date], [z] FROM (SELECT row_number() over (order by Serial_number) as r,*,@idx1 as z from  [crm].[stg_dr_transactions]) as m where r=@abc1
  -- SET @abc4='INSERT into [crm].[stg_dr_transactions_updated] SELECT * FROM (SELECT row_number() over (order by Serial_number) as r,*,'+@idx1+ ' as z from  [crm].[stg_dr_transactions]) as m where r='+@abc1
  -- print @abc4
   END

  while @idx!= 0
   begin

   set @idx=charindex('|',@idx1)
   set @idx1=@idx1
   if @idx!=0
            set @slice = left(@idx1,@idx - 1)
else
          set @slice = @idx1
IF len(@idx1)='0' break
declare @SQLstrs1 nvarchar(4000)
DECLARE @abc3 varchar(4000)

--SET @abc3='dfhegffe'

--SET @abc3='INSERT into [crm].[stg_dr_transactions_updated] SELECT * FROM (SELECT row_number() over (order by Serial_number) as r,*,'+@slice+ ' as z from  [crm].[stg_dr_transactions]) as m where r='+@abc1
--print @abc3
INSERT into [crm].[dr_transactions_updated]([r], [Order_Number], [Product_ID], [SKU], [Sales_Key], [Reporting_Date], [z])
SELECT [r], [Order_Number], [Product_ID], [SKU], [Sales_Key], [Reporting_Date], [z] FROM (SELECT row_number() over (order by Serial_number) as r,*,@slice as z from  [crm].[stg_dr_transactions]) as m where r=@abc1
set @idx1 = right(@idx1,len(@idx1) - @idx)
END
SET  @abc1=@abc1
+1
END