Question 285565 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – How to create an external table using variables in Synapse without specifying columns

I have a stored procedure in Synapse Dedicated pool which takes a parquet file from adls and creates a staging table in dedicated pool:

ALTER PROCEDURE dbo.Staging_Tables_sp
(
    @TableName VARCHAR(MAX),
    @SchemaName VARCHAR(MAX)
)
AS
BEGIN

    EXEC ('SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
   
    CREATE EXTERNAL TABLE [' + @SchemaName + '].[Staging_' + @TableName +']
    WITH (
    LOCATION = N''/Folder/ParquetFiles/' + @TableName + '/'',
    DATA_SOURCE = [gold_parquet],
    FILE_FORMAT = [ParquetFormat],
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0)');
END

Execute statement:

DECLARE @TableName varchar(max)
DECLARE @SchemaName varchar(max)
 
 
 EXECUTE [dbo].[Staging_Tables_sp]
    @TableName = 'Table',
    @SchemaName = 'Schema'

When I try to execute it I get the following error:

Parse error at line: 10, column: 22: Incorrect syntax near ‘)’.

Tags:

2

Answers


  1. 0

    In T-SQL, to include a single quote character in a string, you need to escape it by doubling it, you did it at the beginning of the line, but it is missed at the end

    So, you can modify this line

    LOCATION = N''/Folder/ParquetFiles/' + @TableName + '/'',

    With this

    LOCATION = N''/Folder/ParquetFiles/' + @TableName + '/''',
    Login or Signup to reply.
  2. 0

    I got your example working by adding a ‘select’ to query the parquet file using a view.

    CREATE EXTERNAL TABLE [' + @SchemaName + '].[Staging_' + @TableName +']
WITH (
LOCATION = N''/results/' + @TableName + '/'',
DATA_SOURCE = [ResultsDataSource],
FILE_FORMAT = [CSV])
AS
SELECT * FROM vwContacts');

    view:

    CREATE VIEW vwContacts
AS SELECT * FROM
    OPENROWSET(
        BULK 'https://[mystorage].blob.core.windows.net/[container]/contacts.parquet',
        FORMAT='PARQUET'
    ) as [contacts]
GO
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search