Issue with 'CREATE EXTERNAL TABLE' - Azure SQL single DB

Question

Hi, I have a logical Azure SQL server with two databases. One is TestDB and another is Microsoft's sample db 'WideWorldImporters-Standard'. I want to link 'WideWorldImporters-Standard' from TestDB. I was able to create the external data source. But while trying to create an external table using the script below,

CREATE External TABLE [Purchasing].[PurchaseOrders](
	[PurchaseOrderID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[OrderDate] [date] NOT NULL,
	[DeliveryMethodID] [int] NOT NULL,
	[ContactPersonID] [int] NOT NULL,
	[ExpectedDeliveryDate] [date] NULL,
	[SupplierReference] [nvarchar](20) NULL,
	[IsOrderFinalized] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Purchasing_PurchaseOrders] PRIMARY KEY CLUSTERED 
(
	[PurchaseOrderID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
( DATA_SOURCE = WideWorldImportersExternalDS)

========================== I'm getting an error 'Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.' Could you please help me to fix this problem. Thanking you in advance. Regards, Partha

Answer 1

@Partha Das were you able to create an External table? based on the document provided I tried with a sample and was able to create it successfully.

CREATE EXTERNAL TABLE externaltable
(
ID INT,
NAME VARCHAR(20),
LASTNAME VARCHAR(30),
CEL VARCHAR(12),
EMAIL VARCHAR(60),
USERID INT
)
WITH
(
    DATA_SOURCE = externaldb,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'externaltable'
);

Regards

Geetha