locked
how to insert new row between rows in sql server RRS feed

  • Question

  • how to insert new row between rows in sql server

    example:

    column : ID                 address    before               ID               address    after                          

                  1                   street1                               1                street1                                            

                  2                  street2                                2                street5    this row new add                                          

                  3                  street3                                3                 street2                                            

                                                                                 4                 street3      

     help query                                      

    note : VALUE [ID] = int (PRIMARY KEY)

    Can Added by example above?
    • Edited by srajmuneer Thursday, December 1, 2011 3:18 AM
    Wednesday, November 30, 2011 5:15 PM

Answers

  • Please ask SQL questions in the T-SQL forum.

     

    UPDATE ... SET Id = Id + 1 WHERE Id > 1;
    
    INSERT ....
    

     

    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Wednesday, November 30, 2011 7:26 PM
    Answerer
  • Almost done Brian

    create table t (id int,c1 char(1))

     

    insert into t values (1,'a')

    insert into t values (2,'b')

    insert into t values (3,'c')

     

     

    --insert char d at position 2

    declare @pos int

    set @pos =2

     

    insert into t select @pos,'d'

     

    update t set id=id+1 where id>=@pos and 

    c1<>'d'

     

    select * from t order by id

     

     

    drop table t


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Thursday, December 1, 2011 6:50 AM
  • Almost done Brian

    create table t (id int,c1 char(1))

     

    insert into t values (1,'a')

    insert into t values (2,'b')

    insert into t values (3,'c')

     

     

    --insert char d at position 2

    declare @pos int

    set @pos =2

     

    insert into t select @pos,'d'

     

    update t set id=id+1 where id>=@pos and 

    c1<>'d'

     

    select * from t order by id

     

     

    drop table t


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Thursday, December 1, 2011 6:50 AM

All replies

  • Please ask SQL questions in the T-SQL forum.

     

    UPDATE ... SET Id = Id + 1 WHERE Id > 1;
    
    INSERT ....
    

     

    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Wednesday, November 30, 2011 7:26 PM
    Answerer
  • Almost done Brian

    create table t (id int,c1 char(1))

     

    insert into t values (1,'a')

    insert into t values (2,'b')

    insert into t values (3,'c')

     

     

    --insert char d at position 2

    declare @pos int

    set @pos =2

     

    insert into t select @pos,'d'

     

    update t set id=id+1 where id>=@pos and 

    c1<>'d'

     

    select * from t order by id

     

     

    drop table t


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Thursday, December 1, 2011 6:50 AM
  • Almost done Brian

    create table t (id int,c1 char(1))

     

    insert into t values (1,'a')

    insert into t values (2,'b')

    insert into t values (3,'c')

     

     

    --insert char d at position 2

    declare @pos int

    set @pos =2

     

    insert into t select @pos,'d'

     

    update t set id=id+1 where id>=@pos and 

    c1<>'d'

     

    select * from t order by id

     

     

    drop table t


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by srajmuneer Thursday, December 1, 2011 2:21 PM
    Thursday, December 1, 2011 6:50 AM