Insert into a row at specific position into SQL server table with PK

user_648610 0
Viewed 36243

I want to insert a row into a SQL server table at a specific position. For example my table has 100 rows and I want to insert a new row at position 9. But the ID column which is PK for the table already has a row with ID 9. How can I insert a row at this position so that all the rows after it shift to next position?

rows insert sql-server sql
6 Answers
DECLARE  @duplicateTable4 TABLE (id int,data VARCHAR(20))
INSERT INTO @duplicateTable4 VALUES (1,'not duplicate row')
INSERT INTO @duplicateTable4 VALUES (2,'duplicate row')
INSERT INTO @duplicateTable4 VALUES (3,'duplicate rows')
INSERT INTO @duplicateTable4 VALUES (4,'second duplicate row')
INSERT INTO @duplicateTable4 VALUES (5,'second duplicat rows')
DECLARE  @duplicateTable5 TABLE (id int,data VARCHAR(20))

insert into @duplicateTable5 select *from @duplicateTable4
delete from @duplicateTable4


declare @i int , @cnt int
set @i=1
set @cnt=(select count(*) from @duplicateTable5)
while(@i<=@cnt)
begin
if @i=1 
begin
insert into @duplicateTable4(id,data) select 11,'indian' 
insert into @duplicateTable4(id,data) select id,data from @duplicateTable5 where id=@i
end
else
insert into @duplicateTable4(id,data) select id,data from @duplicateTable5 where id=@i
set @i=@i+1
end


    select *from @duplicateTable4
user_89185410

This kind of violates the purpose of a relational table, but if you need, it's not really that hard to do.

1) use ROW_NUMBER() OVER(ORDER BY NameOfColumnToSort ASC) AS Row to make a column for the row numbers in your table.

2) From here you can copy (using SELECT columnsYouNeed INTO ) the before and after portions of the table into two separate tables (based on which row number you want to insert your values after) using a WHERE Row < ## and Row >= ## statement respectively.

3) Next you drop the original table using DROP TABLE.

4) Then you use a UNION for the before table, the row you want to insert (using a single explicitly defined SELECT statement without anything else), and the after table. By now you have two UNION statements for 3 separate select clauses. Here you can just wrap this in a SELECT INTO FROM clause calling it the name of your original table.

5) Last, you DROP TABLE the two tables you made.

This is similar to how an ALTER TABLE works.

Admin1
Related