how to generate id in a specified format in sql.

how to generete id in a specified format in sql database table,other than auto generated ids?

globally unique Identifier (GUID)

Declare @GUID as uniqueidentifier

SELECT @GUID = newid()

if i want to create like book1, book2 etc

May be a trigger?

A wired try here.

INSERT INTO test (name) SELECT 'Book' + CONVERT(VARCHAR, MAX(id+1)) FROM test 

I hate this anyway. Performance as well as concurrency reasons.

Create a Tabel with a Column  LastValue.

Create a Trigger On_INSERT and Increment the LastValue of the Table.

use this  Value as   SELECT 'book' + @LastValue

Hi Anish,

check out the following link.

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Hope this helps.

Smitha

What about creating an Identity column and concating it with the text? Or I will prefer Praveen's way, but concurrency will be an issue there.

Better use identity column only and when you want to display data use "Book"+id. Like:

SELECT 'Book' + CONVERT(VARCHAR, id) FROM test

Well all depends on your requirements.

thanks all.i got it.

ET IDENTITY_INSERT ON Place this statement before your insert statement, it allows you to enter specific id's into an identity seed column. Place this line immediately after your insert statement. SET IDENTITY_INSERT

OFF New id's will continue to be generated for new inserts. I am assuming there will be a one time insert of existing customers and equipment from some other legacy system? If this is going to be a bolt on to an existing system, then you don't need to generate new id's they are already being generated so you just need a unique index.

Regards

FELIX

@Felix

If you meant @@IDENTITY, it works only after an INSERT operation. If we use this, it will be a double work as we will have to do INSERT first, then do an UPDATE.

We usually look for less overhead methods.

Wll, I am not sure whether I got correctly what you meant. Sorry.

Hi Felix

You have copied the test from this link. Please post source when you copy from other sites.

http://www.daniweb.com/forums/thread93042.html#

oh man...

@Felix: as Shoban said, always publish the source if you copied it from somewhere. Otherwise is is illegal. Read this: Plagiarism