Surrogate Primary Key

• wi_marti

  Newbie

  Points: 5

  More actions

  February 13, 2004 at 9:21 am

  #160422

  Hi, I'm (oviously) new to SQL Server 2000, and need to do something like Next Key Table as discussed at:

  http://www.sybase.com/detail?id=860

  1> begin tran

  2> declare @next_key

  3> update next_key set next_key = next_key + 1

  4> select @next_key = next_key + 1

  5> from next_key

  6> insert into data_table (key_column, ...)

  7> values (@next_key, ...)

  8> commit tran

  We need to generate our own unique key in the form of yy-hex (04-f8) where it's year dash incrementing-hex-number. We'd like to have a next_key table with only one field and value, the value would just change with each request, instead of appending. Our vb6 would pass parameters to update a sqls 2k data table, somehow also placing the unique key, and output the key to the program for updating additional tables as necessary. Is the above sample a stored procedure, UDF or something else? Me and Query Analyzer can't seem to make any sense of it. It'd be nice if it could all be done with one procedure. Can anyone provide some syntax for me? Thanks!

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 13, 2004 at 1:36 pm

  #494056

  This Thread has ALL you need

  Good Luck!

  
  * Noel