Insert Row Records into Columns.

The Source data is Like:store in Retention_Import_Staging Table

Now We have to convert above data into below format and load data into Retention_Dates table

By Using the Store Procedure we implement the above task:

IF EXISTS (SELECT       *

                              FROM dbo.sysobjects

                              WHERE       id = object_id(N'[dbo].[TS_pCN_Dates_Load]')

                              AND   OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

      DROP PROCEDURE [dbo].[TS_pCN_Dates_Load]]

      PRINT 'Deleted script ''dbo.TS_pCN_Dates_Load'''

END

GO

SET   QUOTED_IDENTIFIER ON

GO

SET   ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[TS_pCN_Dates_Load]

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      DECLARE @RowData varchar(4000)

      DECLARE @Col1 varchar(50)

      DECLARE @Col2 varchar(50)

      DECLARE @Col3 varchar(50)

      DECLARE @Col4 varchar(50)

      DECLARE @SplitOn varchar(3)

      DECLARE @Row_id int    

      DECLARE @array_text varchar(4000)

      DECLARE @Error_fg varchar(2)

SET @Error_fg = (select top 1 error_fg from SFI_Retention_Import_Staging where error_fg ='Y')

IF ISNULL(@Error_fg,'')=''

BEGIN

      ----------------fetch records from SFI_Retention_Import_Staging table using cursor------------------

      DECLARE Retention_Cursor CURSOR FOR

      SELECT Row_id,array_text from Retention_Import_Staging

      OPEN Retention_Cursor

      FETCH NEXT FROM Retention_Cursor INTO @Row_id,@array_text

      WHILE @@FETCH_STATUS = 0

      BEGIN

           

      SET @SplitOn = ','

      SET @RowData = @array_text + ','

      SET @Col1 = NULL

      SET @Col2 = NULL

      SET @Col3 = NULL

      SET @Col4 = NULL 

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col1 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE 

      BEGIN

            SELECT @Col1 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

     

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col2 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE

      BEGIN

            SELECT @Col2 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

      IF (@RowData = '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

      While (Charindex(@SplitOn,@RowData)>0)

      Begin

            SET @Col3 = NULL

            SET @Col4 = NULL

            SELECT @Col3 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            if @col3 = ''

            set @col3 = NULL

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

                 

            IF (Charindex(@SplitOn,@RowData)>0)

            BEGIN

                  SELECT @Col4 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

                  if @col4 = ''

                  set @col4 = NULL

                  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

            END

            INSERT INTO dbo.Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            VALUES(@Col1,@Col2,@Col3,@Col4)

      End

      IF (@RowData <> '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

      ------------------------------------------------------------------------------------

FETCH NEXT FROM Retention_Cursor INTO @ROW_ID,@array_text

      END

      CLOSE Retention_Cursor

      DEALLOCATE Retention_Cursor

      ---update trigger_date field------------------

      UPDATE Retention_Dates

      SET TRIGGER_DT= (SELECT convert(VARCHAR(10),TRIGGER_DT,101))

END  

      DELETE FROM Retention_Dates

      WHERE ROW_ID IN(

            SELECT row_id

            from

                  (

                  Select row_id,ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT,rank() over (partition by ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT order by row_id) as rnk

                  from Retention_Dates

                  )aa

            WHERE aa.rnk > 1)

END