sql add record at specific row rather than append to bottom of table

[omar999]

hi guys - this is a newbie question so bear with me..

i have a table containing approx 90 records - each has a unique ID which increments from 1 to 90.

isit possible to add a new record after a specified record/row? so like add record 91 after record 10 for example and then the unique ID field to adjust the numerical order?

thanks in advance
Omar.

[ck9663]

SQL Server does not have sequential arrangement of data. It only looks like it because it access it that way and return it that way.

But if that's your requirement, you still can. Don't use the unique ID as the primary key. Use something else and just create a unique index for that ID instead.

Happy Coding!!!

~~ CK

[omar999]

thanks for the reply CK - do you mind elaborating as I'm not sure how to go about setting another unique index.. im a little confused..

thanks in advance

[ck9663]

Sorry, I read your requirement again and it looks like you don't need another index.

To accomplish your goal you have to do it in two steps.
1. set all ID to ID+1 where ID >= yournewID.
2. insert yournewid

Now, to make sure that there will be no duplicate, you need to have a unique index on ID. The problem is when there will gaps on your sequence.

Happy Coding!!!

~~ CK