March 18, 2009 at 11:00 am
Okay... so the user decides the sequence. If he says the sequence number is a 2 and there is already a sequence 2 in the existing data... what determines whether it should come before or after the existing 2 ?? Is there a tiebreaker of some sort? Or does it matter?
Slow down and think. We have lots of experience with these types of requirements.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 11:02 am
The user defines the sequence, it isn't alphabetical. So when inserting a record the user would give 2 as the sequence, and the rest of the records have to be re-sequenced
Even when inserting one item at a time, you are still giving yourself a lot of extra complications. Let alone if you are inserting (as recommend by us) several records at the same time. It sounds to me that you are forcing the user to do the job of the server...
It might be workable for a very limited set of records in the entire table, but still I do not see the point of having a user specifying a sequence. Would you tell us why you need this ? This would allow us to help you.
March 18, 2009 at 11:04 am
foxjazz (3/18/2009)
OK DAMNI don't have it all wrong, but here is the issue.
The user defines the sequence, it isn't alphabetical. So when inserting a record the user would give 2 as the sequence, and the rest of the records have to be re-sequenced.
I have to ask at this point, why does the user specify the sequence of the parts? Is there a business reason for this, for instance preferred suppliers listed first, or whatever? Knowing this will also help in developing a valid solution to the problem.
March 18, 2009 at 11:05 am
Just
UPDATE
sequence = sequence + 1
WHERE sequence >= @Sequence
and then insert your new record.
You do not need a loop for this
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 11:08 am
foxjazz (3/18/2009)
let me give some datapartnumber, producttype, sequence
7401,belt,1
7432,belt,2
8401,hose,1
8422,hose,2
This is the table.
inert a record (8403,hose,?)
after a resequence you would have
8401,hose,1
8402,hose,2
8422,hose,3
But what Bob stated was exactly what you're asking here. What significance does the sequence give to your data?
CREATE TABLE #Prd(
partnumber INT NOT NULL,
producttype VARCHAR(20) NOT NULL,
sequence INT NOT NULL
)
INSERT INTO #Prd
SELECT 7401,'belt',1 UNION ALL
SELECT 7432,'belt',2 UNION ALL
SELECT 8401,'hose',1 UNION ALL
SELECT 8422,'hose',2
SELECT *
FROM #Prd
insert INTO #Prd
SELECT 8403,'hose',-50 -- -50 is a random value to show sequence is not meaniful and can be eliminated with the proper use of an Order By Clause
SELECT PartNumber, producttype
FROM [#Prd]
WHERE producttype = 'hose'
ORDER BY producttype, PArtnumber
DROP TABLE [#Prd]
March 18, 2009 at 11:11 am
Hey Fox. Don't be overwhelmed by the rush of answers and questions after each post. There are a lot of people watching and we aren't coordinating our efforts. I'm typing at the same time as Lynn and J.
Be flattered you are getting so much attention and the really quick replies. It can't last indefinitely, but people are responding to your change of heart. These forums work better if you slow down the pace between your posts.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 11:14 am
Lynn Pettis (3/18/2009)
I wrap my entire store procedures in a BEGIN END block, why? It clearly delineates the stored procedure for one. Also, somewhere in another thread, I think Jeff Moden actually found a slight performance gain be doing it as he never did it either. This is something that probably should be investigated in more depth in a different thread.
Thanks Lynn, I'll track this down 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2009 at 11:26 am
foxjazz (3/18/2009)
let me give some datapartnumber, producttype, sequence
7401,belt,1
7432,belt,2
8401,hose,1
8422,hose,2
This is the table.
inert a record (8403,hose,?)
after a resequence you would have
8401,hose,1
8402,hose,2
8422,hose,3
If you must do this, then the following, based on Bob's code will do what you want
set nocount on
create table #parts (partnumber varchar(10), productType varchar(10), sequence int)
insert into #parts
select 7401,'belt',1 union all
select 7432,'belt',2 union all
select 8401,'hose',1 union all
select 8422,'hose',2
select partnumber,productType,sequence from #parts
order by PARTNUMBER
insert into #parts
select 8403,'hose',0
--
set nocount off
--
-- This does the resequence.
-- Normally, you'd do it after multiple changes, not just 1 insert.
--
update #parts
set sequence = n.seq
from
#parts a
join (
select
count(*) as 'seq', x.partnumber, x.productType
from
#parts x, #parts y
where
x.productType = y.productType and x.partnumber >= y.partnumber
group by x.partnumber, x.productType
) n
on a.partnumber=n.partnumber and a.productType=n.productType
where
a.sequence <> n.seq
--
-- Resequence complete
--
set nocount on
select partnumber,productType,sequence from #parts
order by PARTNUMBER
drop table #parts
/*------------------------
Results
--------------------------
partnumber productType sequence
---------- ----------- -----------
7401 belt 1
7432 belt 2
8401 hose 1
8422 hose 2
--
(2 row(s) affected)
--
partnumber productType sequence
---------- ----------- -----------
7401 belt 1
7432 belt 2
8401 hose 1
8403 hose 2
8422 hose 3
------------------------*/
I've used SET NOCOUNT ON and set NOCOUNT OFF so you can see that only the 2 records which needed resequencing were changed.
Derek
March 18, 2009 at 11:41 am
Thanks Derek. I hadn't had the time to do that, and couldn't have improved on it.
Fox, if you look at that last code you will see that Derek took an entire query, wrapped it in parentheses, and called it N, so he could join to it just as if it were a physical table. It's called a subquery. Don't let the slightly more advanced techniques throw you off your game. Just know that they do exist and can be learned one at a time.
I worry that we are all hitting you with a firehose of information. It makes it hard to swallow. Sometime soon, please take the time to do the pop quiz that I posted for you this morning. The typing will be minimal, but it's the thinking that's important.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2009 at 12:14 pm
Um I don't quite understand and it's my fault for not getting my last req accross.
Forget producttype..
lets say we just have partnumber, seq
and we want to insert a part at a certain seq location.
something like:
select partnumber from tbl order by seq where seq > 2
update tbl set seq = tbl.seq + 1 where seq > 2
insert into tbl (partnumber,seq) values ('newpn', 2)
ok we can see that we can insert 1 record this way.
Lets now say that we have 2 records to insert at seq=2 and 3
so would something like this work?
update tbl set seq = tbl.seq + (select count(*) from insertdata) where seq > 2
insert into tbl (partnumber,seq) select partnumber, rowcount() + 1 from insertdata order by seq
does this make sense to anyone?
March 18, 2009 at 12:21 pm
I would keep it simple. Deal with one record at a time. If your Stored Procedure is designed to work fine with one record you can call it sequentially with all the other records. The user interface's job should be to feed you the data the way you want it. If you design you database interface to accept one or two or more orders at the same time you are creating more problems than you probably are willing to solve.
(And then you will have a reason to rant about SQL...)
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 12:26 pm
One correction to your pseudo code
update tbl set seq = tbl.seq + 1 where seq > 2
Should be
update tbl set seq = tbl.seq + 1 where seq >= 2
because you want to move the record with seq = 2 as well to make room for the new record.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 12:27 pm
Do the sequences start over in certain ranges? For example, would parts in a certain class be sequence 1-10, and in another class, you might start over at 1 and go up to 10 again? Or is the sequence table-wide?
In either case, I'd probably handle it in SQL 2000 with a trigger that works with the inserted table, and in SQL 2005/2008 by using the inserted table in the insert proc. But the specific mechanics of the trigger depend on whether there are ranges, and how they are identified.
Also, it will matter if an insert has sequence 2 and 4, and both of those already exist. In that case, the current 2 becomes 3, but does the current 3 become 4 and the inserted 4 becomes 5, or does the current 3 become 5 and the inserted 4 stays as 4?
Alternately, instead of incrementing existing values, would 2 replace the current 2 and 4 replace the current 4, becoming an update instead of an insert if values for those already exist?
Those are business rules that would have to be accounted for in the code.
I've actually had to build something like this (which became completely obsolete with the advent of Row_Number in SQL 2005), and I can modify it for your need, if you define those rules.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2009 at 1:12 pm
GSquared (3/18/2009)
in SQL 2005/2008 by using the inserted table in the insert proc..
Gsquared, are you saying that in 2005/2008, I can access the inserted and deleted tables directly from a procedure?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 18, 2009 at 1:20 pm
Greg Snidow (3/18/2009)
GSquared (3/18/2009)
in SQL 2005/2008 by using the inserted table in the insert proc..Gsquared, are you saying that in 2005/2008, I can access the inserted and deleted tables directly from a procedure?
Yep. Definitely can.
insert into dbo.MyTable (MyCol)
output inserted.ID
select MyOtherCol
from dbo.MyOtherTable;
Note the "output" section. You can access inserted and deleted values in there. Can use "output ... into..." to insert them into a table (including table variable, temp table and real table), or can just use "output" to select them.
Works with insert, update and delete. Same rules as triggers when it comes to which ones are available for which actions.
Very, very cool feature. No more using cursors to grab multiple identities if you need them for a second table or some such.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 256 through 270 (of 465 total)
You must be logged in to reply to this topic. Login to reply