createviewsdynamically

----05. 
CREATE TABLE #t (
    is_hidden bit NOT NULL, 
    column_ordinal int NOT NULL, 
    name sysname NULL, 
    is_nullable bit NOT NULL, 
    system_type_id int NOT NULL, 
    system_type_name nvarchar(256) NULL, 
    max_length smallint NOT NULL, 
    precision tinyint NOT NULL, 
    scale tinyint NOT NULL, 
    collation_name sysname NULL, 
    user_type_id int NULL, 
    user_type_database sysname NULL, 
    user_type_schema sysname NULL, 
    user_type_name sysname NULL, 
    assembly_qualified_type_name nvarchar(4000), 
    xml_collection_id int NULL, 
    xml_collection_database sysname NULL, 
    xml_collection_schema sysname NULL, 
    xml_collection_name sysname NULL, 
    is_xml_document bit NOT NULL, 
    is_case_sensitive bit NOT NULL, 
    is_fixed_length_clr_type bit NOT NULL, 
    source_server nvarchar(128), 
    source_database nvarchar(128), 
    source_schema nvarchar(128), 
    source_table nvarchar(128), 
    source_column nvarchar(128), 
    is_identity_column bit NULL, 
    is_part_of_unique_key bit NULL, 
    is_updateable bit NULL, 
    is_computed_column bit NULL, 
    is_sparse_column_set bit NULL, 
    ordinal_in_order_by_list smallint NULL, 
    order_by_list_length smallint NULL, 
    order_by_is_descending smallint NULL, 
    tds_type_id int NOT NULL, 
    tds_length int NOT NULL, 
    tds_collation_id int NULL, 
    tds_collation_sort_id tinyint NULL
)
GO

INSERT INTO #t EXEC sp_describe_first_result_set N'
   SELECT * FROM 
    OPENROWSET 
    (
        BULK ''sourcedatasystem/Sales_Customers/*.csv'',
        DATA_SOURCE = ''ExternalDataSourceDataLake'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE,
        FIELDTERMINATOR =''|''
    ) AS fct'

DECLARE @mincol INT,
        @maxcol INT,
        @sqltext NVARCHAR(4000)

SELECT @mincol = MIN(column_ordinal) FROM #t
SELECT @maxcol = MAX(column_ordinal) FROM #t

set @sqltext = 'CREATE VIEW LDW.vwSalesV2' + CHAR(10) + CHAR(13) + 'AS' + CHAR(10) + CHAR(13) + 'SELECT '

WHILE @mincol <= @maxcol
BEGIN
    SELECT @sqltext = @sqltext + CONCAT('CAST(',[name],' AS ',system_type_name,') AS ',[name],CASE WHEN @mincol = @maxcol THEN '' ELSE ',' END)
    FROM #t
    WHERE column_ordinal = @mincol

    SET @mincol = @mincol +1
END

SET @sqltext = @sqltext + ' FROM 
    OPENROWSET 
    (
        BULK ''sourcedatasystem/Sales_Customers/*.csv'',
        DATA_SOURCE = ''ExternalDataSourceDataLake'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE,
        FIELDTERMINATOR =''|''
    ) AS fct'

EXEC sp_executesql @tsl = @sqltext