I am receiving data in the following format:
ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 2
3 9 3
4 2 Blue Ski 3 1
5 4 2
6 0 3
I want to fix the table so it looks like this:
ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 1 2
3 1 9 3
4 2 Blue Ski 3 1
5 2 4 2
6 2 0 3
I wrote this query, but need to speed it up as it takes around 2.5 hours to run. (there are around 170,000 rows).
Here is the query I use, can anyone help me speed it up?
DECLARE @ID varchar(255), @INV varchar(255), @nINV varchar(255)
DECLARE kirk_cursor SCROLL CURSOR FOR
SELECT [ID], ItemID FROM kStoreQty order by [ID]
OPEN kirk_cursor
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
WHILE @@FETCH_STATUS = 0
BEGIN
IF @INV IS NULL
BEGIN
UPDATE kStoreQty SET
ItemID = @nINV
WHERE [ID] = @ID
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
ELSE
BEGIN
SELECT @nINV = @INV
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
END
CLOSE kirk_cursor
DEALLOCATE kirk_cursor
ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 2
3 9 3
4 2 Blue Ski 3 1
5 4 2
6 0 3
I want to fix the table so it looks like this:
ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 1 2
3 1 9 3
4 2 Blue Ski 3 1
5 2 4 2
6 2 0 3
I wrote this query, but need to speed it up as it takes around 2.5 hours to run. (there are around 170,000 rows).
Here is the query I use, can anyone help me speed it up?
DECLARE @ID varchar(255), @INV varchar(255), @nINV varchar(255)
DECLARE kirk_cursor SCROLL CURSOR FOR
SELECT [ID], ItemID FROM kStoreQty order by [ID]
OPEN kirk_cursor
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
WHILE @@FETCH_STATUS = 0
BEGIN
IF @INV IS NULL
BEGIN
UPDATE kStoreQty SET
ItemID = @nINV
WHERE [ID] = @ID
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
ELSE
BEGIN
SELECT @nINV = @INV
FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
END
CLOSE kirk_cursor
DEALLOCATE kirk_cursor