SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding a Sequence Number to the records from LKUP No match output


Adding a Sequence Number to the records from LKUP No match output

Author
Message
MuraliKrishnan1980
MuraliKrishnan1980
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1409
Hi Friends,

I am migrating data from one table to another and I am doing a lookup to find if the record already exists or not. I would insert the records in the destination if it is not present there.

The Destination table does not have an identity field. Hence, i would have to increment the PK value myself before inserting. I stored the max of the PK in a variable. I am trying to add a sequence number to the LKUP no match output so that i can create the PK for the new records with the logic max(PK) + Sequence number. However, i am not able to add a sequence number to no match output.

Is it possible to do the way i wish or is there any other better solutions.

Any help would be appreciated.

Thanks
Murali
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 3037
The Destination table does not have an identity field. Hence, i would have to increment the PK value myself before inserting


This sounds like a nightmare waiting to happen for you!

Is there any reason why you can't add an auto inc onto your destination table as the current structure will be horrible to maintain..

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
MuraliKrishnan1980
MuraliKrishnan1980
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1409
You are absolutely correct Andy and we have already informed the same to our client. But for some reason they have gone by this design.
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 3037
Any reason why you can't use the PK from the source table as the PK in the destination?

But for some reason they have gone by this design.


Crazy! Crazy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
MuraliKrishnan1980
MuraliKrishnan1980
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 1409
Well. The destination would be having data from multiple sources.. so i cant go with the PK from source...I found a way to achieve this in script task. But wanted to know if there is a way to do this using ssis itself
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 3037
Because I don't know your structure it's a little difficult, but...

I have had to do similar things in the past (although I insisted on design changes) however a concatenation of Source + Source PK could be used?

If source is not an integer I would be tempted to maintain a lookup table for sources with a key for source and just append that to the source table(s) PK and do the
insert into the destination..

Others may have other ideas (or I may just be shot down in flames for mine ;-) )

Edit: sorry I didn't mean append a composite PK would be a better choice..

Andy:-)

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search