generate specific format in sql table

sk_ko

Mycroft Holmes

I suspect this is a BAD idea, I also suspect you are using this field as a primary key. If so the DON'T it is a BAD design. To do this you will need to query the table to find out how many inserts there are for this year, build your string and insert the record. There are a number of alternatives to this process that include triggers and stored procedures. Here is the reason it is a bad idea. What happens if another user inserts a record during this process, BOOM one busted database. I suggest you insert the datetime for Created then query filtered on the year and ordered by the created date. If you need an incremental number you can use ROW_NUMBER() assuming you are using sql server

Never underestimate the power of human stupidity RAH

sk_ko

hi, I am not clear your answer... ok, how about adding auto generated id(identity) column in my table.. I will use id column as primary key. I will not use applicationID column as primary key. for applicationID column, I just want to store in this format. id | name | date | applicationID 1 | Willam | 2011-12-03 | 201100001 2 | Susan | 2011-12-04 | 201100002 3 | Alex | 2012-01-01 | 201200001 4 | Mata | 2013-01-01 | 201300001 pls.

Mycroft Holmes

Better design! Then you can update the record after it is inserted to calculated the applicationid field. You will need to pad the ROW_NUMBER()/MAX value with leading zeros and combine that with the year datepart. This is a better option as the ApplicationID field that is a human consumed piece of info is not used to identify the record.

Never underestimate the power of human stupidity RAH

PIEBALDconsult

Here's a similar thread from a few months back: http://www.codeproject.com/Messages/4031000/Re-generating-IDs-with-IDENTITY-in-sql-server-2005.aspx[^]

sk_ko

I saw same problem like me. but I didn't see best answer, like sample.

jschell

There is also a security reason for not doing this. If someone sees that their id is "20110005" it is easy to guess that 20110004 and 20110006 are also valid.

Lost User

sankooo wrote:

I want to add one column in sql table.
That will be in specific format.

Mycroft is right, but it's half the story. Tables hold data, not information.

sankooo wrote:

If the year changes to new year, I want to start from 00001. like that.

That's a composite column, and in the ideal world we try to keep the data atomic. That means that you can loose the date-part, as that's already encoded in the previous column. You also don't need the prefix-zeroes, those are merely there for presentation. Hence, all you need in the ApplicationID-row is a sequence-number; all other information is already present and the complete applicationID as you need can be calculated from the rest of the tuple. The other half of the story is that we don't want to duplicate information, as that would be redundant (and thus, increases the chances for errors). I'd suggest you read up on normalization[^] procedures.

Bastard Programmer from Hell :suss: