January 25, 2017 at 12:05 pm
Hi,
i have daily job which copies data from production to uat.i want to know know what are chnages on the uat data after job completion.
Can anyone provide script for this.
here is tables
prod :Tab1
ID,Name,Date,TaxId
uat:Tab2
Id,Name,Date.TaxId
i want to know if TaxID is updated on the uat i want to know the ID of that.
Can anyone help me
January 25, 2017 at 12:22 pm
Sree Divya - Wednesday, January 25, 2017 12:05 PMHi,i have daily job which copies data from production to uat.i want to know know what are chnages on the uat data after job completion.
Can anyone provide script for this.here is tables
prod :Tab1
ID,Name,Date,TaxIduat:Tab2
Id,Name,Date.TaxIdi want to know if TaxID is updated on the uat i want to know the ID of that.
Can anyone help me
The answer, as usual, is "it depends". How, exactly, is the data copied? If the UAT table is just truncated and then the data INSERTED, then you have no way to know what was there before. You'd have to change the copy process to do something to output all the new rows, which means you'd have to compare values by some method that touches both PROD as well as UAT. On the other hand, if the data is being copied into UAT via an SSIS package, it may be possible to modify that package to push that PROD data into a staging table in UAT, and then compare that data with the existing UAT data, and generate the differences, before finally either merging that data with UAT or doing the equivalent of an "UPSERT". to the UAT table.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 25, 2017 at 12:28 pm
after data copying to uat server i want retrive the report what are IDs are updated today.
January 25, 2017 at 1:29 pm
Sree Divya - Wednesday, January 25, 2017 12:28 PMafter data copying to uat server i want retrive the report what are IDs are updated today.
That much was clear already. But you need to respond to Steve's points.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 6:55 am
Sree Divya - Wednesday, January 25, 2017 12:28 PMafter data copying to uat server i want retrive the report what are IDs are updated today.
You said the same thing in your original post. The bottom line is that you need to have some way to keep track of what was present in that table prior to when the update takes place, which is why it matters as to exactly HOW that update takes place. If it's just a "truncate the table and insert all the PROD records", then that process will HAVE to change in order to figure out the differences. If it's a MERGE statement, then there may be more pieces of the process that need to change. However, since you didn't answer my first post with any details on HOW that update takes place, anything else I might suggest would be mere guesswork. Help us help you by providing the necessary level of detail. SQL Server doesn't keep track of old data for you, so there aren't any odd T-SQL statements you've never heard of (or that any experts are aware of) that can retrieve the data from before it was over-written, unless you've told SQL Server to do so, as with the CDC (aka Change Data Capture) feature. As that feature requires a fair amount of effort and conscious choice to invest that effort to get it set up and working, and given that there are other usually easier ways to go about it, it's probably overkill. Again, we can't know anything about your environment that you don't tell us about.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply