Using CTAS statement maintaining the original primary key - Synapse SQL pool

Question

Using CTAS statement maintaining the original primary key - Synapse SQL pool

pmscorca 1,032

Hi,

In a Synapse dedicated SQL pool is it possible to use the CREATE TABLE AS SELECT statement maintaining the original primary of the source table?

Thanks

Smaran Thoomu 21,600 Reputation points Microsoft External Staff

2024-03-18T03:15:01.96+00:00

@pmscorca Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Smaran Thoomu 21,600 Reputation points Microsoft External Staff

2024-03-18T03:15:01.96+00:00

@pmscorca Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Azar 26,820 MVP

Hey there pmscorca

Thats a good question and thanks for using QandA platform

I guess yees, you can use the CREATE TABLE AS SELECT (CTAS) statement to create a new table while maintaining the original primary key of the source table. but, you need to make sure that you define the primary key constraint in the new table. like this (see below code)

CREATE TABLE new_table
WITH
(
    DISTRIBUTION = HASH(primary_key_column) 
)
AS
SELECT *
FROM source_table
WHERE 1=0; 
ALTER TABLE new_table ADD CONSTRAINT PK_new_table PRIMARY KEY (primary_key_column);
INSERT INTO new_table
SELECT *
FROM source_table;

If this helps kindly accept the answer thanks much.

Azar 26,820 Reputation points MVP

2024-03-18T15:04:24.95+00:00

Hey checking if this helped you, kindly accept the answer if it did, thanks much.

Share via

Using CTAS statement maintaining the original primary key - Synapse SQL pool

1 answer

Your answer