QUERY TO GENERATE TABLE SCRIPT

Question

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

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)

Answer 1

Hi @Sidney

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.

Answer 2

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.