Capturing identity column using transform data task.
-
I am using a transform data task to migrate data from one table to another. Is there a way to capture between each insert statement the last identity column value of the table? Between each insert I need to populate another table with the last identity column value. I tried searching the forums for an answer but couldn't find anything. Sorry if this question has already been asked. Thanks for you help.
-
Why not just add a line in that inserts into the desired table after each insert:
select max(Identity_Column) from yourtable. Will give you the last id entered.
Or is there more to your question.....
------------------------------
The Users are always right - when I'm not wrong! -
I guess I just don't know where I would specify these extra queries. The only options I have for the transformation data task are source, destination and transformation.
Using the data driven query task I see where I can add the extra queries. Only problem using this option is I can't figure out how to disable the identity insert.
Hope this all makes sense.
-
Still not entirely sure what you are trying to do.
Are you using a data driven query task for the package as a whole or do you have various source and destination connections that you execute in separate steps of the package?
Also, are you inseting into a singletable from multiple tables?
------------------------------
The Users are always right - when I'm not wrong! -
Have a look at IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY() in Books-Online
Win
-
Just to try and understand your question.
If you have 100 records to migrate, does that mean you want to update the other table 100 times, or do you just want to update it at the end.
If it's the latter, add an ExecuteSQL task after your Datapump task to run the update statement. Using this method, the "max(identity_column)" query as suggested by Jonathan would probably be your best bet.
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply