SQL - Want to make your Alias Column Name dynamic?

I was dealing with a not very cleaned CSV file, which is comma delimited, with "" around each column data, yet, data itself has comma, and worse, even double quote.

sample data:

It is hard for SSIS packages to load such a CSV file clean without column shifting. So what I did was to load the data into just one column, and try to separate them to each column in SQL.

From

To

Here is the way I do it: I combined cursor and dynamic SQL to make the column alias name dynamic:

/****** Script for SelectTopNRows command from SSMS  ******/

IF OBJECT_ID('tempdb..#Names') IS NOT NULL DROP TABLE #Names;

create table #Names ( ColID INT, ColName VARCHAR(50));

INSERT INTO #Names (ColId, ColName)   

Values(1,'[APINo]'),  (2,'[FileNo]'),(3,'[CurrentOperator]'), (4,'[CurrentWellName]'), (5,'[LeaseName]'),(6,'[LeaseNumber]'),

      (7,'[OriginalOperator]'),(8,'[OriginalWellName]'), (9,'[SpudDate]'),(10,'[TD]'),  (11,'[CountyName]'), (12,'[Township]'),

      (13,'[Range]'),(14,'[Section]'), (15,'[QQ]'), (16,'[Footages]'),(17,'[FieldName]'), (18,'[ProducedPools]'),

      (19,'[OilWaterGasCums]'),(20,'[IPTDateOilWaterGas]'),(21,'[Wellbore]'),(22,'[Latitude]'), (23,'[Longitude]'),

      (24,'[WellType]'), (25,'[WellStatus]'), (26,'[CTB]'),  (27,'[WellStatusDate]');

  

SELECT ColId, ColName 

from #Names;

-------------Calculate distance based on county---------------

DECLARE @ColId INT;

DECLARE CURSOR_1 CURSOR 

LOCAL STATIC READ_ONLY FORWARD_ONLY

FOR 

SELECT DISTINCT ColId

FROM  #Names

OPEN CURSOR_1;

FETCH NEXT FROM CURSOR_1 INTO @ColId;

WHILE @@FETCH_STATUS = 0

BEGIN 

    

DECLARE @ColName VARCHAR(50);

DECLARE CURSOR_2 CURSOR 

FOR 

SELECT DISTINCT ColName

FROM  #Names 

WHERE colId =   @ColId

OPEN CURSOR_2;

FETCH NEXT FROM CURSOR_2 INTO @ColName;

WHILE @@FETCH_STATUS = 0

BEGIN 

DECLARE @sql NVARCHAR(max)

DECLARE @column_alias VARCHAR(50)

SET @column_alias = @ColName

SET @sql = '

with tbl_raw as(

SELECT *

from [dbo].[ND_WellIndex_Load]

), tbl_extract as (

select a.id,right(a.raw_data,len(a.raw_data)-charindex(''",'',a.raw_data)-1) as raw_data,left(a.raw_data,charindex(''",'',a.raw_data)) as' + @column_alias +

'from tbl_raw a 

)

update a

set a.raw_Data = b.raw_data ,a.' + @column_alias + '= b.' + @column_alias +

'from tbl_raw a,

tbl_extract b

where a.id = b.id '

exec sp_executesql @sql

FETCH NEXT FROM CURSOR_2 INTO @ColName;

END;

CLOSE CURSOR_2;

DEALLOCATE CURSOR_2;

        FETCH NEXT FROM CURSOR_1 INTO @ColId;

END;

CLOSE CURSOR_1;

DEALLOCATE CURSOR_1;