Member_2_7966113
asked on
SQL Change Tracking Issue pt3
Experts I'm going resubmit a too question on Change Tracking with fresh data
The table structure I'm working with is as follows:
Before doing any modifications to the dataset it looks as follows
The actual dataset is as follows:
Now, I have modified the dataset as follows:
From the above you can see I have inserted a row 'InsertedCol', I have updated a row at PersonID 5 'eeee-updatedCol', and I have deleted a row at PersonID 4 'dddd'.
I have then executed the following code:
You can see everything is working fine, but where it should say 'dddd' in the name column is gives the results (null).
I need 'dddd' where the null is.
Can you help me with that?
The table structure I'm working with is as follows:
CREATE TABLE PrestigeCars.dbo.data_source_table (
PersonID INT NOT NULL
,Name VARCHAR(255) NULL
,Age INT NULL
,PRIMARY KEY CLUSTERED (PersonID)
) ON [PRIMARY]
GO
ALTER TABLE dbo.data_source_table
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
GO
Before doing any modifications to the dataset it looks as follows
The actual dataset is as follows:
create table data_source_table
(
PersonID int NOT NULL,
Name varchar(255),
Age int
PRIMARY KEY (PersonID)
);
INSERT INTO data_source_table
(PersonID, Name, Age)
VALUES
(1, 'aaaa', 21),
(2, 'bbbb', 24),
(3, 'cccc', 20),
(4, 'dddd', 26),
(5, 'eeee', 22);
Now, I have modified the dataset as follows:
From the above you can see I have inserted a row 'InsertedCol', I have updated a row at PersonID 5 'eeee-updatedCol', and I have deleted a row at PersonID 4 'dddd'.
I have then executed the following code:
SELECT
ChVer = SYS_CHANGE_VERSION,
ChCrVer = SYS_CHANGE_CREATION_VERSION,
ChOp = SYS_CHANGE_OPERATION, PA.*
FROM CHANGETABLE(CHANGES dbo.data_source_table, 29) AS ChTbl
LEFT JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID
And the results are as follows:
You can see everything is working fine, but where it should say 'dddd' in the name column is gives the results (null).
I need 'dddd' where the null is.
Can you help me with that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ben you really nailed it for me. I was struggling to comprehend this, but your answer made it very clear.
I just have one thought. I got the original idea from the following link https://solutioncenter.apexsql.com/reading-sql-server-change-tracking-results/
Can you explain how they were able to see deleted rows?
I just have one thought. I got the original idea from the following link https://solutioncenter.apexsql.com/reading-sql-server-change-tracking-results/
Can you explain how they were able to see deleted rows?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Everything is working as designed. You will only see those that have been inserted or updated, but not deleted because the source table doesn't have the key.