Inserting a row between ordered records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting a row between ordered records

Author

Topic

omnisysgroup
Starting Member

10 Posts

Posted - 2008-01-23 : 15:10:40

I have an ordered product list table called Product

Product Order
Apples 1
Oranges 2
Peaches 3
Lemons 4

How do I write a SQL statement to insert the values of 'bananas' after 'oranges'?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 15:18:32

Is this your desired output?

Product Order
Apples 1
Oranges 2
Bananas 3
Peaches 4
Lemons 5

omnisysgroup
Starting Member

10 Posts

Posted - 2008-01-23 : 15:20:46

yes that is my desired output.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 15:56:53

[code]DECLARE @Fruits TABLE ( [Product] CHAR(10), [Order] INT )
INSERT @Fruits ( [Product], [Order] )
SELECT 'Apples', 1 UNION
SELECT 'Oranges', 2 UNION
SELECT 'Peaches', 3 UNION
SELECT 'Lemons', 4

UPDATE @Fruits
SET [Order] = [Order]+1
WHERE [Order]>(SELECT [Order] FROM @Fruits
WHERE [Product] = 'Oranges')

INSERT @Fruits ([Product], [Order])
SELECT 'Bananas', [Order]+1
FROM @Fruits
WHERE [Product] = 'Oranges'

SELECT * FROM @Fruits
ORDER BY [Order][/code]

omnisysgroup
Starting Member

10 Posts

Posted - 2008-01-23 : 16:05:40

thanks much you are awesome...one more question now...say my table has a billion rows and this process becomes very resource intensive...how could i modify my table structure to better suit these type of updates?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 16:11:28

Surely there must be a more logical column to order your data by than the Order column. What do you base your decision of what to set the Order column to? How do you decide that Bananas go before Peaches?

omnisysgroup
Starting Member

10 Posts

Posted - 2008-01-23 : 16:15:44

it is an interview question so i have clue.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 16:31:46

I would suggest doing some reading on indexes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources