CODEWITHSUNDEEP
sqlsql-serverdatabaseazure-synapse
Khawaja Abdul Ahad
Khawaja Abdul Ahad

Reputation: 49

"Distribution Option" error in "CREATE TABLE table AS" in SQL Server

I have been facing an issue with using CREATE TABLE AS. I have made three tables in a database. I intend to join the tables based on policy number and create a new table out of the joined data.

CREATE TABLE [dbo].[New_Customer_Segmentation_Data] AS 
( 
SELECT *
FROM [dbo].[CustomerSegmentation_updated]
INNER JOIN [dbo].[DimLapse]
ON [dbo].[CustomerSegmentation_updated].[PolicyNumber] = [dbo].[DimLapse].[Policy Number] 
INNER JOIN [dbo].[dimclaim]
ON [dbo].[DimLapse].[Policy Number]  = [dbo].[dimclaim].[POLICYNUMBER]
)

It returns the error "'Distribution' option must be explicitly specified in "CREATE TABLE AS SELECT" and "CREATE MATERIALIZED VIEW" statements." Can anyone guide me on this error?

Upvotes: 3

Views: 7793

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Just like the error message says, you must specify a Distributed table design option with CTAS, eg from the docs:

CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
 DISTRIBUTION = ROUND_ROBIN
 ,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales];

CREATE TABLE AS SELECT

Upvotes: 3

Related Questions