Multiple Increment fields 1

[Giselleai]

I'm trying to create a table with two increment fields in MS SQL Server 2000. However, it would reset incrementation of one field instead of two. Is there another way to increment more than one field? Thanks.

 

[BogdanMBM]

Hi!

I'm confused. Are you talking about 2 Identity columns in the same table? In that case what seems to be the problem?
Please be more explicit!

Bogdan.

 

[Sabnac]

From BOL :
IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

So if you want another autoincrement, you have to manage it yourself

 

[BogdanMBM]

My mistake, Sabnak!

Still, Giselleai can use something like this:

Have one Identity column in the main table.
Have another table with 2 columns: one Identity and the 2'nd of any type.
First insert some value in the second table. Then run the insert statement against the main table, using @identity function for the second incremental column. This way Giselleai has the identity value generated by the first insert statement (in the second table) to use for the main table for the second incremental column...

Is this a good idea?

 

[dmhirsch]

I can't see the point of two identity columns in one table, because they would fill with identical values.

 

[Giselleai]

Thanks for the replies. I was hoping there was another method, instead of having to use another table.

 

[harry1aussie]

Try using this in a trigger



declare @intCounter int
set @intCounter = 0
update tblYourTable
SET @intCounter = YourField = @intCounter + 1