Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mivey4
Yak Posting Veteran
66 Posts |
 Posted - 2012-09-11 : 09:37:59
|
| I have a table with potentially billions of records. The requirement is to set certain fields for a group of records within the table to a previous value until that value changes, then replace the previous value with the new value.Consider the following as an example of the records being retrieved from the original source:Event EventDesc DescId Desc2Id Desc3Id Desc4Id Desc5Id---------------------------------------------------------------1 EventOne | 1 | 102 | 1 | 1 | 1 |2 EventTwo | 2862 | 1 | 1 | 1 | 131 |3 EventThree | 1 | 1 | 1 | 1 | 1 |6 CloseRecord | 1 | 102 | 1 | 102 | 1 |1 EventOne | 1 | 105 | 1 | 1 | 1 |2 EventTwo | 2873 | 1 | 1 | 1 | 131 |3 EventThree | 1 | 1 | 106 | 1 | 1 |6 CloseRecord | 1 | 101 | 1 | 102 | 1 |The desired output after insertion into the final table would be:Event EventDesc DescId Desc2Id Desc3Id Desc4Id Desc5Id---------------------------------------------------------------1 EventOne | 1 | 102 | 1 | 1 | 1 | 2 EventTwo | 2862 | 102 | 1 | 1 | 131 |3 EventThree | 2862 | 102 | 1 | 1 | 131 |6 CloseRecord | 2862 | 102 | 1 | 102 | 131 |1 EventOne | 1 | 105 | 1 | 1 | 1 |2 EventTwo | 2873 | 105 | 1 | 1 | 131 |3 EventThree | 2873 | 101 | 106 | 1 | 131 |6 CloseRecord | 2873 | 101 | 106 | 102 | 131 |As you can see, each field should acquire the value of the previous field's record unless it is a value of one. Also, the range of records that repeat this rule are grouped and range from 1 through 6 as the Event value.Is there an SSIS component that can gracefully accomplish this task during the initial record insertions with minimal complexity as opposed to performing complex SQL updates to the table after it's population?It was suggested to me that the Cache Transform task may be the solution but after reading up on how to implement it, it doesn't seem to be the appropriate solution for the problem.If there isn't any SSIS package task that could accomodate as a solution, what are some other possible alternatives? I am open to any reasonable suggestions.ThanksOracle OCAAdaptec ACSP |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-11 : 11:26:50
|
| looks like a logic you need to implement using execute sql task to me------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
mivey4
Yak Posting Veteran
66 Posts |
Posted - 2012-09-11 : 12:13:22
|
| Thanks visakh16.Those are my exact thoughts as well but I wanted to affirm by confirmation of several other Developers before concluding that T-SQL would be the only viable option.Oracle OCAAdaptec ACSP |
|
|
|
|
|
|
Is there a SSIS Component for Dynamic Updates