1

I m trying to create temporal table in azure synapse datawarehouse.

    CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

i m getting parse error enter image description here

Vaishnavi S
  • 25
  • 1
  • 5

2 Answers2

0

As of August 2023, the Microsoft Learn (documentation) page Transact-SQL features supported in Azure Synapse SQL does not explicitly mention temporal tables or the FOR SYSTEM_TIME clause, but the section on SELECT statements does mention that

SELECT statement is supported, but some Transact-SQL query clauses [...] are not supported.

The documentation page for temporal tables themselves lists which versions of SQL Server it applies to, and does not include Synapse DW in that list: screenshot from Microsoft Learn, "Temporal Tables" page: Applies to SQL Server 2016 and later, Azure SQL Database, and Azure SQL Managed Instance

Combined with the fact that you got a syntax error when you tried, it seems certain that this language feature is not currently supported in Azure Synapse DW. This may, of course, change in the future.

Stuart J Cuthbertson
  • 390
  • 5
  • 13
-1

Yes It is Possible to create Temp Tables in the Azure Synapse. Here is the query to create Temporary tables.

CREATE  TABLE #Department (

ID INT  PRIMARY  KEY,

Name  VARCHAR(50),

Manager VARCHAR(50)

);

In order to create a Temp table you will have to Include the (#)hash or Pound Symbol in front of the Table name as it indicates the temp table.

enter image description here

enter image description here

Also you can use the fully qualified server name to query.

like for example select * from tempDb#department

This should help you create temporary tables In Azure Synapse.

DileeprajnarayanThumula
  • 643
  • 2
  • 5
  • Hi DileepRaj, Thank you for the reply. But i was enjuring about the temporal table creation in Azure Synapse Analytics not able creating temporary table [System versioning] https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 – Vaishnavi S May 09 '23 at 16:26