Create Model: SandBox
Create Entity: ChangeTrackingTest,
Staging Table: SandBox_ChangeTrackingTest
Create Code values Automatically: Yes
Start with: 1
Enable explicit hierarchies and collections: No
Add Attribute
ChangeTrackingTest.TestAttribute
Free-From
DataType: Text
Enable change tracking: Yes
Change tracking group 1
Edit
ChangeTrackingTest.Code
Enable change tracking: Yes
Run the following Script
--Load Value
DECLARE @BatchTag NVARCHAR(50)
SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
INSERT INTO [stg].[SandBox_ChangeTrackingTest_Leaf]
(
[ImportType],[ImportStatus_ID],[BatchTag],[Code],[Name]
)
SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', '8' AS 'Code', 'Test' AS 'Name'
EXEC stg.udp_SandBox_ChangeTrackingTest_Leaf 'VERSION_1', 0, @BatchTag
GO
--Set To NULL
DECLARE @BatchTag NVARCHAR(50)
SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
INSERT INTO [stg].[SandBox_ChangeTrackingTest_Leaf]
(
[ImportType],[ImportStatus_ID],[BatchTag],[Code],[Name]
)
SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', '8' AS 'Code', '~NULL~' AS 'Name'
EXEC stg.udp_SandBox_ChangeTrackingTest_Leaf 'VERSION_1', 0, @BatchTag
GO
--Set To NULL this should ignore based on ImportType
DECLARE @BatchTag NVARCHAR(50)
SELECT @BatchTag = REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
INSERT INTO [stg].[SandBox_ChangeTrackingTest_Leaf]
(
[ImportType],[ImportStatus_ID],[BatchTag],[Code],[Name]
)
SELECT 0 AS 'ImportType', 0 AS 'ImportStatus_ID', @BatchTag AS 'BatchTag', '8' AS 'Code', NULL AS 'Name'
EXEC stg.udp_SandBox_ChangeTrackingTest_Leaf 'VERSION_1', 0, @BatchTag
GO