QUERY TO GENERATE TABLE SCRIPT

Sidney 40 Reputation points
2023-03-13T21:45:18.5466667+00:00

I have a table I am populating from the metadata structure of files in my Azure DataLake.

User's image

Using this script I am trying to generate a table. Not sure what I am missing but it seems as it only deletes the records in the table.

DECLARE @ExecTime [datetimeoffset](7);
set @ExecTime = '2023-03-13 21:11:20.2393117 +00:00';
DECLARE @tableName varchar(50)
SET @tableName = 'ssz_active                                                                                                                                                                                                                                                     ';
DECLARE @sqlCommand nvarchar(max)
DECLARE @folderPath nvarchar(255);
SET @folderPath = 'raw/school_data/'

SET
  @sqlCommand = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @tableName + ']'') AND type in (N''U''))
  CREATE EXTERNAL TABLE [dbo].[' + @tableName + '] (' 
 
WHILE((SELECT COUNT(*) FROM tables_to_create WHERE executeTime = @ExecTime) > 0)
BEGIN
  DECLARE @key int
  SELECT
    @key = MIN(fieldOrder)
  FROM
    tables_to_create
  WHERE
    executeTime = @ExecTime
     
  DECLARE @fieldName VARCHAR(50)  
  DECLARE @translatedType VARCHAR(50)
 
  SELECT
    @fieldName = fieldName,
    @translatedType = translatedType
  FROM
    tables_to_create
  WHERE
    fieldOrder = @key
    AND executeTime = @ExecTime
 
  SET
    @sqlCommand = @sqlCommand + '
          [' + @fieldName + '] [' + @translatedType + '] NULL'
 
  DELETE FROM
    tables_to_create
  WHERE
    fieldOrder = @key
    AND executeTime = @ExecTime
 
  IF((SELECT COUNT(*) FROM tables_to_create WHERE executeTime = @ExecTime) > 0)
    SET
      @sqlCommand = @sqlCommand + ', '
END
 
SET
  @sqlCommand = @sqlCommand + '
  )
  WITH
  (
    LOCATION = ''/' + @folderPath + ''',
    DATA_SOURCE = DataLakeStaged,
    FILE_FORMAT = StagedParquet
  )'
   
--EXEC(@sqlCommand)
Print (@sqlCommand)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,488 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    2023-03-14T02:00:52.36+00:00

    Hi @Anonymous

    User's image

    Why use translatedtype here? As far as I known, shouldn't it be fieldtype if you want to create table?

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-03-13T21:58:22.35+00:00

    Add some debug PRINTs to your code to see what is it doing. What does your final PRINT say?

    By the way, instead of all this bracket stuff, use quotename. For instance:

    SELECT quotename('My Funny Table')
    

    returns

    [My Funny Table]

    And if you have

    SELECT quotename('Right Bracket]')
    

    It handles that too.