Tuesday, July 6, 2021

Synapse - CREATE TABLE as SELECT with Distribution - Not recognized error [Solved]

Scenario: While trying to create a backup of a table in one Synapse schema into another backup  table  in another schema,  

example syntax below,




Msg 103022, Level 16, State 1, Line 4
Parse error at line: 2, column: 8: DISTRIBUTION is not a recognized table or a view hint.Msg 103022, Level 16, State 1, Line 4 
Parse error at line: 2, column: 8: DISTRIBUTION is not a recognized table or a view hint.


Solution: To resolve the issue change the syntax from existing 

CREATE TABLE mySchema.bkp_Students AS SELECT * FROM enrollment.Students

WITH ( DISTRIBUTION=HASH(Student_id)

CLUSTERED COLUMNSTORE INDEX )  


to

CREATE TABLE mySchema.bkp_Students 

WITH ( DISTRIBUTION=HASH(Case_id),

CLUSTERED COLUMNSTORE INDEX )  

AS SELECT * FROM enrollment.Students



A table needs to have a default distribution before data can be inserted into it.

No comments:

Post a Comment