Insert data in the middle of any table

Question

I have a Countries(ID,Code,Name,....) table.here ID is a auto generated primary key column.

this are few rows:

{1,AD,Andorra};
{2,AG,Antigua and Barbuda};
{3,AI,Anguilla}

now the problem is I cant find a way to add a new country Code, say 'AH' in such a way that the automatically the table become like..

{1,AD,Andorra} 
{2,AG,Antigua and Barbuda}
{3,AH,......}
{4,AI,Anguilla}

in other words I need sorted list by ID and CODE.Its ok if I need to change some ID values. I have a clustered index on ID.

plz,plz suggest me a way...

Why do you need the `ID` column for sorting purposes at all? If you really need something different than the `Code` column you can add another column `SortIndex`. — Tim Schmelter, Jan 16 '13 at 11:50

score 4 · Answer 1 · edited Jan 16 '13 at 11:58

4

ID (as a standard primary key) is not intended for sorting purposes. You should not rely on it. I'd recommend you to add SortIndex field instead. Then in two easy queries like

UPDATE Countries set SortIndex = SortIndex+1 WHERE SortIndex>= [your index]

And

INSERT INTO Countries VALUES (...)

You will get what you need. Also please don't forget to add ORDER BY SortIndex in select query.

edited Jan 16 '13 at 11:58

hsuk

6,770
13
50
80

answered Jan 16 '13 at 11:47

Mikhail Payson

923
8
12

score 3 · Answer 2 · answered Jan 16 '13 at 11:50

Changing the PK value of an entity like this is likely to head for a world of pain. Once the ID has been assigned to a particular entity, it shouldn't change.

e.g. you have another table that has a FK to a country id. IN your example, those other rows that related to Anguilla would now relate to the country "AH" represents.

It sounds like you are interested in the alphabetical ordering of the country codes. So for sorting purposes you just need to ORDER BY Code - you shouldn't change the PK values to suit your required sort order

score 1 · Answer 3 · answered Jan 16 '13 at 11:52

Can you please explain why you want to do this? It makes no sense in my opinion. Maybe you should add a "SortOrder" column or something instead? If the ID is a part of some sort of business rule you should, if possible, change the design.

Edit: As already answered in a few other posts :)

score 0 · Answer 4 · answered Jan 16 '13 at 11:46

0

You can update the table before inserting the new record.

update Countries set id = id + 1 where id >= 3;

Then,

insert into Countries values (3,'AH','AH');

answered Jan 16 '13 at 11:46

Orangecrush

1,970
2
15
26

if you wish to do this don't forget to set identity insert on --> SET IDENTITY_INSERT Countries ON (but i would suggest to look at the other answers and take an other route) – ufosnowcat Jan 16 '13 at 14:49

Insert data in the middle of any table

4 Answers4

Linked