Column ID in a Table wiht autoincrement value predetermined

[javierarka]

I need to create a Column id in my table. This Column ID has to be a 'sa0200701260001' where sa0 is a value i give to it 20070126 is the current date and 0001 is a number that will increment everytime this statement is called can somebody tell me how can i make this incrmented value. Thanks Javierarka

[andyharman]

Which database engine are you using? If you were using a SQL-Server stored procedure then the code would look something like:

create procedure MyRecordInsert
@Prefix varchar(3),
@OtherData varchar(20)
as begin
   set nocount on
   declare @PrimaryKey varchar(15), @MaxKey int
   Begin tran

   --Create first part of primary key (prefix + date).
   set @PrimaryKey = @Prefix + Convert(varchar(8), GetDate(), 112)

   --Find the higher number that has been allocated today.
   --Use locking to prevent anyone else inserting until we have finished
   --our transaction.
   select @MaxKey = Convert(Int, Substring(Max(PrimaryKey), 12, 4))
      from MyTable with (tablock, holdlock)
      where PrimaryKey like @PrimaryKey + '%'

   --Increment the highest number, and then append to key.
   set @MaxKey = IsNull(@MaxKey, 0) + 1
   set @PrimaryKey = PrimaryKey +
         Right('0000' + Convert(varchar(4), @MaxKey), 4)

   --Insert record into the database.
   insert into MyTable (PrimaryKey, OtherData)
      values (@PrimaryKey, @OtherData

   Commit tran
end

You will need to add some error handling. The general approach is valid for other database engines (although the syntax would obviously be different). The combination of a transaction and locks should allow you to allocate new keys without clashing with other users. If you only have a small number of users then you might be able to get away with a simpler approach. Hope this is helpful. Andy

[Pete OHanlon]

You could always do it using something like this:

INSERT INTO myTable(ID, Item1, Item2)
SELECT 'sa0' + CONVERT(DATETIME, GETDATE(), 112) + COUNT(*),
@Item1, @Item2
FROM myTable

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.