Inserting new row in the middle and not at the end

Question

Post reply

Inserting new row in the middle and not at the end

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Daniel McCann-223910 Newbie Points: 6 More actions · Answer 1

Obviously a bad idea, but had to kill some time before work, so tried this for fun.

---------

create proc fruitput

@new int, --id wanted for new input

@newval varchar(100) --new value to insert

as

set nocount on

select * from fruit

insert into fruit (fruit)

select @newval

update fruit

set fruit= b.fruit

from fruit join fruit b

on fruit.id = b.id+1

where b.id >= @new

update fruit set fruit = @newval where id = @new

select * from fruit

--------------

exec fruitput 3, 'apple'

Have the select *'s before and after to compare. Do with as you will.

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 2

Here is a solution(steps listed)

1. Change the schema, add another column say "PriorityOrder"

2. Insert all the values (initial data insert)

3. Create a Trigger on this table, such that if anybody inserts a row, and if the NEW ROWS "PriorityOrder" is the one which already exits in the table, then increment the value in this column = existing value + 1, for the row which have values greater than the one you are trying to insert.

Hope this helps

paul

fasttrack SSCertifiable Points: 6071 More actions · Answer 3

Thank to everybody for your suggestions.....

I 'll follow your instruction to reach my goal......

(I thought another way.....to build an easy worksheet and then to import....in SQL as a table).....

Just to explain why I need an order......

I have a large database on which a BCP and Bulk must be run.

To avoid to write the same code for every table (more than 400), I have realized to use a table (called CATALOG table - containing all tables on which the bcp will be performed).

Of course all tables are linked by constraint (PK and FK) and so they must to respect an exact order to be processed.

If the database should change...I mean a table is added, it means that the catalog table must be modify, and the name of new table will be added not at the end but in the right position.........(keepeing the PK and FK order)

Thats the reason I asked an help about this.....I know that tables contains records and not an exact order, but in this case I need it.......

Thank again......

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 4

Personally I'd Use Joe's suggestion of adding another column for explicit order, and make it a decimal type so it's easy to add new table names at any point in the sequence. That way your script can just order by that column, and as you need to add new table names later you can put them in the midst of the others just by using values like 5, 6, 6.5, 6.8, 7 in the order column.

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

I think using a table and assign a sequence with a ordering value (1.0, 2.0,3.0.....n.0, 1.1,4.3....) it should be the right solution.