September 18, 2008 at 5:39 am
i have to update my database in such a way that if the record is not there in the database i have to add it but if it is there then i have to change its version ie i have to add 1 in its older version.There is no version in my file from where i am inserting the records.So i have to access it from destination only.But i don know how to access version.Plz amswer it.
September 18, 2008 at 9:28 am
Have you got an example of the data and your schema? You'll get better responses the more information you can provide.
For instance, what identifies a record as new? what identifiers are used? etc etc
September 18, 2008 at 3:27 pm
You should be able to use the OLE cmd in a Data Flow Task and isolate the record with the highest version using a subquery and the OVER clause.
Such as :
Update t
Set t.version = t.version + 1
from
schema.table t
inner join
(Select
keyValue
,max(Version) OVER(partition by keyValue) as maxVersion) ss
on t.keyValue = ss.keyValue
where t.version = maxVersion
and t.keyValue = ?,...
Then map the incoming data to the "?" as needed to satisfy the WHERE clause
Remember mapping parameters to "?" is done by the order that the "?" appear in the SQL command
Because of the multiple version of the records the group by on the OVER clause should not include version nor should the "keyValue" field.
This is a row by row operation so the performance will not be great.
Also not all OLE connections will allow use of parameters but SQL Server should be OK.
Hope this helps - Good Luck
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply