December 19, 2019 at 3:58 am
I'm using CTAS concept to do full load and works perfectly. Now, they want to load the data incrementally. They will start sending transactionType in the file. For deleted records, it will come with TransactionType of D. For updated records, it will come two rows with I and D, where D being before upate and I being after update. For newly inserted records, it will come with I. I need to load these incremental data to DW but I don't want the D records in DW. How do I accomplish this?
CREATE TABLE Staging.Table1
WITH
(
CLUSTERED COLUMNSTORE INDEX ,
DISTRIBUTION=HASH(ID)
)
AS
SELECT col1,col2,col3
FROM Staging.Table1 WITH(NOLOCK)
UNION ALL
SELECT col1,col2,col3
FROM DW.Table WITH(NOLOCK) AS P
WHERE NOT EXISTS
( SELECT *
FROM Staging.Table1 S
WHERE S.ID = P.ID
)
December 20, 2019 at 4:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 24, 2019 at 11:48 am
You can maybe tweak your code to create a new warehouse table and swap it with the existing one. The code below is from a Microsoft article on CTAS, specifically using it for merging data - https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-ctas
CREATE TABLE dwh.[Table1_upsert]
WITH
( DISTRIBUTION = HASH([ID])
, CLUSTERED INDEX ([ID])
)
AS
-- New rows and new versions of rows
SELECT col1
, col2
, col3
FROM stg.[Table1] AS s
UNION ALL
-- Keep rows that are not being touched
SELECT col1
, col2
, col3
FROM dwh.[Table1] AS p
WHERE NOT EXISTS
( SELECT *
FROM [stg].[Table1] s
WHERE s.[ID] = p.[ID]
);
RENAME OBJECT dwh.[Table1] TO [Table1_old];
RENAME OBJECT dwh.[Table1_upsert] TO [Table1];
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply