Home Why a System-Versioned temporal table cannot be merged?
Reply: 0

Why a System-Versioned temporal table cannot be merged?

user2597
1#
user2597 Published in April 24, 2018, 10:31 am

I have this error which I don't understand quite well. I have the following System-Versioned temporal table:

CREATE TABLE [dbo].[Dim_Table] (
columnA         int             IDENTITY,
--
columnB         nvarchar(10)    NOT NULL,
columnC         nvarchar(10)    NULL,
SysStartTime    datetime2       GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
SysEndTime      datetime2       GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime),
--
CONSTRAINT PK_ColumnB PRIMARY KEY CLUSTERED (ColumnB DESC)
)WITH(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Hst_Dim_Table],
                            DATA_CONSISTENCY_CHECK = ON)
);
GO

And the following external table:

CREATE EXTERNAL TABLE [dbo].[STG_M_Table2]
(
        id                  smallint      NULL,
        columnB             nvarchar(10)  NOT NULL,
        columnC             nvarchar(10)  NULL,
)  
WITH (LOCATION='/STG_M/Table/',   
        DATA_SOURCE = AzureStorage,  
        FILE_FORMAT = TextFileFormat  
);

Both works well separately. But, if I try out doing the following MERGE (or UPDATE Sys-versioned SET FROM external_table) I get the following error:

Msg 7320, Level 16, State 110, Line 6
Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "(null)". Unsupported operation has been executed. The target of an update, delete or insert operation must be a table, not a view. Modify the statement and re-run it.

MERGE statement:

MERGE INTO dbo.Dim_Table           AS Target
USING      dbo.STG_M_Table2        AS Source
ON         Target.columnB = Source.columnB
WHEN MATCHED THEN
    UPDATE
    SET Target.columnC            = Source.columnC,
WHEN NOT MATCHED THEN
    INSERT (columnB, columnC)
    VALUES (columnB, columnC)
;

I searched what is a view since, I'm pretty newbie working with SQL Server and transact SQL. And a fast read about views gave me the following main idea: a virtual table whose contents (columns and rows) are defined by a query... Which I don't understand quite well, how it is related to my System-versioned table.

The issue resides in the System-versioned temporal table, because if I change it to another table, i.e., a temporal or a current table, the code works well.

Can anybody help me? I don't see what I'm doing wrong. Is there any nicety with System-Versioned temporal Tables and update statements?

Thanks in advance!!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.418001 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO