Assigning Auto Value

[tester888]

Hi there!
I am creating tables within my DB and was wondering if theres a way that i can assign a generated primary key which is not an autonumber such as 1,2,3.
I am using the following code and need to have some kind of auto character plus a number generated as a primary key for ComputerID. Any ideas?


CREATE TABLE Computers
(
ComputerID int IDENTITY NOT NULL PRIMARY KEY,
EngineerID varchar(10) NOT NULL

)

 

[SQLBill]

some kind of auto character plus a number

What kind....an example or examples would help.

-SQLBill

Posting advice: FAQ481-4875

 

[tester888]

ok perhaps
C824637,
CI24314
something that is auto generated providing uniqueness to the column?
Is it possible to create this?

 

[SQLBill]

You can always create most anything.....

Once you have the first id, you could do something like:

INSERT myTable myID
 VALUES (SELECT 'C' + SUBSTRING(myID, 2, (LEN(myID)))+1 FROM myTable)

Maybe something like that.....might have to play with it a bit.

-SQLBill



Posting advice: FAQ481-4875

 

[SQLBill]

One mistake.....since you are adding 1 to the substring result, that makes it an integer, so you will need to convert that result back to VARCHAR before concatenating with 'C'.

-SQLBill

Posting advice: FAQ481-4875