I have one table say City having one or more record, now i need to generate the contiguous sequence for each city to be stored in a different table. Is there any way apart from maintaining a separate table to record the next available number or so? This introduces the chances of getting the duplicate numbers being generated. Is there other batter way of implementing the same scenario.
asked Oct 16, 2012 at 12:34 PM in Default
If you mean at the table level then you need to review the column property IDENTITY - http://msdn.microsoft.com/en-us/library/ms186775.aspxIf you want to create a contiguous list of numbers for a recordset then use ROW_NUMBER - http://msdn.microsoft.com/en-us/library/ms186734.aspx
If the number just needs to be auto generated you can look at setting the column's
If you query the above table after inserting one record you should see an ID of 1000 and 1100, then the City set to 'Montgomery' and 'Birmingham'.
answered Oct 16, 2012 at 12:53 PM
Forgive me, I'm not sure that I fully understand the question. Do your tables have an identity column on it? Can you post the DDL for the tables?
Not sure if this applies, but you can use a function such as ROW_NUMBER which would allow you to generate a distinct list of row numbers based off of some partition or order by value.
Depending on the table structure, you could also use a unique non-clustered index or a primary key on the tables to help prevent duplication of values.Just throwing it out there. Hope this helps!
answered Oct 16, 2012 at 12:56 PM
I don't remember the code exactly.... giving you only the idea
Create a table with only one column defined as identity column
Create a procedure that inserts a new record in that table
The procedure returns output of the inserted identity
Use that output as the input in the required table in a procedure
To keep the values unique, use APPLICATION LOCK on the procedure
I hope it will give you the basic idea .......
answered Oct 16, 2012 at 02:10 PM