October 4, 2012 at 12:28 pm
Hi Forum,
I need to load legacy data from 2 sources using 2 SSIS packages to a target table .
I need to generate a surrogate key in the following format
SurrogateKey col1 col2 col3
001-00-000001 X Y Z ---LEGACY DATA A
001-00-000002 U V W ---LEGACY DATA A
001-00-000003 A B C ---LEGACY DATA B
001-00-000004 D E F ---LEGACY DATA B
Once package 1 complete the next max value should be seeded to insert the package 2 values .
Please give me your valuable inputs.
Thanks in Advance
October 4, 2012 at 2:13 pm
Use an Identity column in the target table to get your numerics.
Then use a calculated column on a varchar field to apply that to the formatting you need in the 'real' column.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2012 at 2:27 pm
Hi Kraig,
We cannot create a identity column on the table, it is a varchar field.
Is there any other way we can accomplish this ?
October 4, 2012 at 2:46 pm
Right, use an extra column to create the identities, then a calculated column for your varchar field that converts it into the one you need with formatting
Otherwise, no, not really. At least not easily. You'd need to create what's best known as a sequence and apply that to your formatting. Sequences are, at best, painful to use when you're not used to them.
There are a few forms of sequencing out there, what version of SQL are you in?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply