October 14, 2010 at 11:13 pm
Hi,
Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"PIC0001", "PIC0002", "PIC0003", ............ etc.
Anyone can help me how i can done this one in SSIS.
Thanks.
October 15, 2010 at 1:15 am
rocky_498 (10/14/2010)
Hi,Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"PIC0001", "PIC0002", "PIC0003", ............ etc.
Anyone can help me how i can done this one in SSIS.
Thanks.
First thing I'll point out is that 'incremental load' is a defined term and means something different from what you have asked for.
Second, there is no direct way of using an IDENTITY field to achieve what you want. But you could create a PERSISTED (for speed/indexing purposes) computed column based on an IDENTITY column that should do the job.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 15, 2010 at 4:44 am
I think the best practice here is to create an ID column which is a real IDENTITY column to serve as a primary key with no meaning. Then add your column as a secondary key which is in fact a computed column (persisted), as Phil already mentioned. The expression for the computed column is:
'PIC' + RIGHT('0000' + CONVERT(VARCHAR(4),ID),4)
The convert is used to make sure the value of your ID column is treated as a string. The RIGHT function is used as a little trick to make sure you have always 4 digits with possible leading zero's.
Beware that you are limiting the table to have only 1000 records.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 15, 2010 at 4:50 am
da-zero (10/15/2010)
--Beware that you are limiting the table to have only 1000 records.
10,000, I think you'll find sir 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 15, 2010 at 5:26 am
Phil Parkin (10/15/2010)
da-zero (10/15/2010)
--Beware that you are limiting the table to have only 1000 records.
10,000, I think you'll find sir 🙂
:doze: I blame Friday. And the people who placed two deadlines for me on this Friday.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 15, 2010 at 6:52 am
Thanks for your reply. My target table has field called "P_id" and its "Primary key" and requirement is 'P_id" always auto generate i.e "cba-000001", "cba-000002".
* In target table or source table data the data is more than 70k and its populating every day. I m sorry what did u mean by limited to 10k, how about after 10k?
Thanks.
October 15, 2010 at 7:26 am
rocky_498 (10/15/2010)
Thanks for your reply. My target table has field called "P_id" and its "Primary key" and requirement is 'P_id" always auto generate i.e "cba-000001", "cba-000002".* In target table or source table data the data is more than 70k and its populating every day. I m sorry what did u mean by limited to 10k, how about after 10k?
Thanks.
PIC0000 to PIC9999 is 10,000 records. There is no more room in your key structure beyond that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 15, 2010 at 9:24 am
Why do you need an alpha component in your key? Does each different alpha prefix need to begin incrementing at 1?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply