July 25, 2005 at 1:24 pm
I am trying to create a conversion script to load a table called POLine. The keys are PLPO and PLSeq. When I have multiple lines for a PO, I need to increment the PLSeq by 1. The quanity of data is not large and this is a one-time conversion. I am returning a PLSeq of 1 for each line item. I believe the solution is to commit the changes within the cursor but a BEGIN TRANSACTION...COMMIT runs unusually long.
I would appreciate any helpful hints! Thanks in advance.
Below is my script:
set nocount on
declare @Orderid varchar(10),
@plseq int
DECLARE POLine_Cursor CURSOR FAST_FORWARD FOR
select distinct orderid from lines
OPEN POLine_Cursor
-- Perform the first fetch.
FETCH NEXT FROM POLine_Cursor into @orderid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
If (Select max(plseq) from iams..poline
where PLPO = (select distinct NewPO from transpo where wyzorderid = @orderid)) IS NULL
BEGIN
set @plseq = 1
END
ELSE
set @plseq = (Select max(plseq)+1 from iams..poline
where PLPO = (select distinct NewPO from transpo where wyzorderid = @orderid))
Insert into iams..POLine (PLPO, PLSeq, PLCC, PLQty, PLComment,PLIsNoPackSlip,PLEIChanged,PLIsBuyTry)
select t.NewPO, @PLSeq PLSeq, CostCenter, Quantity, Remarks,0,1,0
from Lines l join transpo t on l.orderid = t.wyzorderid
where l.orderid = @orderid
and len(CostCenter) < 4
and len(Quantity) < 5
and len(Remarks) < 51
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM POLine_Cursor into @Orderid
END
CLOSE POLine_Cursor
DEALLOCATE POLine_Cursor
July 26, 2005 at 12:48 am
Can't say I'm extremly sure what you want, but it seems to me that you should be able to replace this line:
set @plseq = (Select max(plseq)+1 from iams..poline
where PLPO = (select distinct NewPO from transpo where wyzorderid = @orderid))
with:
set @plseq = @plseq +1
which should improve performance somewhat.
/HL
July 26, 2005 at 2:53 am
Maybe....
-- Create temporary table with identity / auto-incrementing number
CREATE TABLE #Conv (ConvID int IDENTITY(1,1), OrderID int)
-- Insert the existing orders, identity values are automatically created
INSERT INTO #Conv (OrderID)
SELECT OrderID FROM lines
-- Insert into the new table
Insert into iams..POLine (PLPO, PLSeq, PLCC, PLQty, PLComment,PLIsNoPackSlip,PLEIChanged,PLIsBuyTry)
select t.NewPO, Conv.ConvID, CostCenter, Quantity, Remarks,0,1,0
from Lines l join transpo t on l.orderid = t.wyzorderid
INNER JOIN #Conv AS Conv
ON Conv.OrderID = l.orderid
where len(CostCenter) < 4
and len(Quantity) < 5
and len(Remarks) < 51
Magic! No cursor.
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply