-3

I have a table with more than 30000 rows.

I need to update the table using a SQL query which required this type of output.

Initial Table :-

  1 , 1 , a ,b
  2 , 1 , b ,c
  3 , 1 , c ,d
  ....
 50 , 1 , b ,n 
 51 , 2 , f ,y
 52 , 2 , g ,o
 53 , 2 , t ,t
 ....
 ....
100, 2 , f, h

Output should be like this :-

1 , 1 , a ,b
2 , 1 , b ,c
3 , 1 , f ,r(new row)
4 , 1 , c ,d
 ....
51 , 1 , b ,n 
52 , 2 , f ,y
53 , 2 , g ,o
54 , 2 , r ,t(new row)
55 , 2 , t ,t
....
....
102, 2 , f, h

So basically first column (primary key) which is auto incrementing should be changed while we enter data based on second column (some type of question Id).

I already seen and referred this question(SQL How to insert a new row in the middle of the table , MySQL syntax for inserting a new row in middle rows?, Insert data in the middle of any table) but they provide solution for smaller tables while I have a table which contains more than 30000 rows.

Can anyone give me any suggestion?

FYI :- I use Microsoft SQL Server.

Community
  • 1
  • 1
mihir6692
  • 177
  • 1
  • 4
  • 19
  • what is logic for inserting new row – Mudassir Hasan Apr 23 '14 at 04:51
  • 1
    Suggestion is **not** to fiddle around with the primary key - let it be! – marc_s Apr 23 '14 at 04:54
  • I need to enter around 100 rows at different places in table. even if i can get idea about how to add a single row , i will be able to run that query with different data around 100 times. – mihir6692 Apr 23 '14 at 05:14
  • Why you want to add records in middle of table? If you want to fetch in some sorting order then you can use ORDER BY clause. Microsoft SQL Server doesn't allow you do such. Though you want to insert into middle of table then you have to turn off the primary key and turn on after insert but be aware that the new records must not violate the definition of Primary Key. – iVad Apr 23 '14 at 05:31
  • Because data will be shown based on sorting of primary key. so if i add data at last position then it will come at last place instead of first palce. – mihir6692 Apr 23 '14 at 05:49

2 Answers2

1

This is bad table design in the first place, because the primary key must not be used in such a way.

This kind of table should have a different column for identifying the order of entries. A Primary key can't provide that functionality, because of it's internal use in the database engine.

JensE
  • 21
  • 2
0

Then you can use this 

    SET IDENTITY_INSERT Table_Name ON
    --Your insert queries over here
    SET IDENTITY_INSERT Table_Name OFF

And make sure that you are not violating the primary key means your data for primary key column must be unique.

iVad
  • 563
  • 2
  • 4
  • 13