generating a key field

for a key in my table I am appending RACT to a number..

So the key would be like this:

RACT100000000000000000000

I would like the next id to be:

RACT100000000000000000001
etc


Is there something in the table create that I could make that happen so that they keep adding one each time? Or is there a better way to do in in my t-sql code where I am inserting?
vbnetcoderAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Not so much your T-SQL as much as Table Design... You could do something like (using more meaningful names of course) :
-- Create a Table with desired attributes

create table My_New_Table 
(Prefix char(4) DEFAULT 'RACT'
,ID bigint IDENTITY
,ColumnA varchar(30)
,ColumnB varchar(30)
,ColumnC int
,PrefixID as Prefix+right('000000000'+convert(varchar(9),id),9), 
CONSTRAINT PK_my_new_table_Prefix_ID PRIMARY KEY CLUSTERED (Prefix,ID)
);

GO  

-- Now populate the minimum required fields

Insert My_New_Table (ColumnA,ColumnB,ColumnC) values ('John','Doe',44)
Insert My_New_Table (ColumnA,ColumnB,ColumnC) values ('Betty','Rubble',2044)

-- And display the results of a query

Select * from My_New_Table

-- Even use the calculated column

Select * from My_New_Table where PrefixId = 'RACT000000002'

-- Although always best to use PK elements 
-- try to get a clustered index seek rather than a scan.
-- which also suggests - do you really need the calculated column ?

Select * from My_New_Table where Prefix = 'RACT' and ID = 2

Open in new window

0
 
Scott PletcherSenior DBACommented:
Use separate columns in the table for the 'RACT' value and the number.  Then you can use IDENTITY or a SEQUENCE to supply values to the number.  Use a concatenated column to return combined values to queries.  You can use a view or a computed column to combine the values.
0
 
vbnetcoderAuthor Commented:
How would I do it if I wrote in my T-SQL code?
0
 
vbnetcoderAuthor Commented:
ty
0
All Courses

From novice to tech pro — start learning today.