question

RohitKulkarni-3496 avatar image
0 Votes"
RohitKulkarni-3496 asked RohitKulkarni-3496 commented

Synapsesql pool

Hello team,
I want to create the GetDate() in synapse sqlpool for one of the column in the table .

I am using this table in data factory pipeline dynamically. So i can't create select statement. So i need to display current date and time in the select statement for column3.So I need to mention in create table statement.
Please advise.
![80351-image.png][1]
Create table Table1
(
column1 [nvarchar](20) NULL,
column2 [int] NULL,
CAST(GETDATE() AS datetime) AS column3
)
WITH ( DISTRIBUTION = ROUND_ROBIN, HEAP )
[1]: /answers/storage/attachments/80351-image.png

azure-data-factory
image.png (6.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered RohitKulkarni-3496 commented

Hey @RohitKulkarni-3496 ,
Unfortunatley synapse doesnt allow functions as default constraints.

 CREATE TABLE dbo.doc_exz (column_a INT, column_b varchar(50));
 GO
 INSERT INTO dbo.doc_exz (column_a) VALUES (7);
 GO
 ALTER TABLE dbo.doc_exz
   ADD CONSTRAINT DF_Doc_Exz_Column_B
   DEFAULT getdate() FOR column_b;
 GO


The above code would work for Azure SQL database but not synapse.
you would get the below error:
An expression cannot be used with a default constraint. Specify only constants for a default constraint.


So ideally the best way would be to insert getdate() into rows while inserting data into synapse table

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.