asked
How to create two identity columns in a table
i want to have two identity columns in my table .one is Primary key ObjectID.2nd one is ObjectName,should like R0000123.i want both these columns as Auto Generated values. i dont want to use any triggers,Sequences on the table. Can you please suggest me how to create the table.
Is the ObjectName based on the ObjectID? If so you could use a computed column
Only one column per table can have an IDENTITY column. If you were in SQL Server 2012 or better, you could take advantage of the SEQUENCE object, but since you're in 2008, that's not possible. Your best bet is either a trigger or a computed column. You can have a table that stores the current max value. Read from that to get the latest for your query and update it after you're done. I know you say you don't want a trigger, but a trigger would work better in this situation. Another option is to break the table apart so you can have an identity column in each table.
You can have a table that stores the current max value. Read from that to get the latest for your query and update it after you're done. This was helpful. Thank You.
i have used the Max value from other table.but i need the output like Obj1 Obj2 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0001 ijk-000 0002 ijk-001 0002 ijk-001 0002 ijk-001 0002 ijk-001 But iam getting like Obj1 Obj2 0001 ijk-000 0001 ijk-001 0001 ijk-002 0001 ijk-003 0001 ijk-004 0002 ijk-005 0002 ijk-006 0002 ijk-007 0002 ijk-008 please help me out this. following is my query. please modify it. select name,'ijk-000'+convert(varchar,col1) as namenew from (select a.*, convert(int,(select max(SUBSTRING(nAME,5,len(NAME))) from table1 where name like 'ijk-%'--+'%[0-9]%' and substring(NAME,5,len(name)) not like '%[A-Z]%') ) + a.[Seq Id] as col1 from table2 A ) c Regards, Sql Server User
15 People are following this question.
