CURSOR speed - SQL 2000 vs. SQL 2005

DECLARE @comments varchar(255)
DECLARE @customer_key varchar(255)
DECLARE @discount varchar(255)
DECLARE @item_code varchar(255)
DECLARE @price_per_item money
DECLARE @order_key varchar(255)
DECLARE @quantity_received varchar(255)
DECLARE @quantity_to_ship varchar(255)
DECLARE @cost money
DECLARE @price_per_item_after_discount money
DECLARE @line_id int
DECLARE @customer_id int
DECLARE @product_id int
DECLARE @quantity int
DECLARE @start_num int
DECLARE @end_num int

DECLARE @ImportCursor CURSOR

SET @start_num = 1
SET @end_num = 10000

WHILE @start_num < 1030000

BEGIN

SELECT * INTO posted_order_items_from_filemaker FROM posted_order_items_from_filemaker2
WHERE imported = '0'
AND id >= @start_num
AND id <= @end_num

SET @ImportCursor = CURSOR FAST_FORWARD
FOR
Select comments, customer_key, discount, item_code, price_per_item, order_key, quantity_received,
quantity_to_ship, cost, price_per_item_after_discount, id
From posted_order_items_from_filemaker

OPEN @ImportCursor
FETCH NEXT FROM @ImportCursor
INTO @comments, @customer_key, @discount, @item_code, @price_per_item, @order_key,
@quantity_received, @quantity_to_ship, @cost, @price_per_item_after_discount, @line_id
WHILE @@FETCH_STATUS = 0
BEGIN

SET @product_id = ''

SELECT @product_id = id
FROM Products
WHERE ProductCode = @item_code

SET @quantity = @quantity_received + @quantity_to_ship

SET NOCOUNT ON
INSERT INTO OrderItems (
OrderID,
ItemID,
Qty,
QtyDelivered,
QtyToSend,
item_price,
item_cost,
comments,
filemaker_discount,
filemaker_price_per_item_after_discount,
import_item,
filemaker_id
) VALUES (
@order_key,
@product_id,
@quantity,
@quantity_received,
@quantity_to_ship,
@price_per_item,
@cost,
@comments,
@discount,
@price_per_item_after_discount,
'1',
@line_id
)

UPDATE posted_order_items_from_filemaker2
SET imported = '1'
WHERE id = @line_id

FETCH NEXT FROM @ImportCursor
INTO @comments, @customer_key, @discount, @item_code, @price_per_item, @order_key,
@quantity_received, @quantity_to_ship, @cost, @price_per_item_after_discount, @line_idEND

CLOSE @ImportCursor
DEALLOCATE @ImportCursor

SET @start_num = @start_num + 10000
SET @end_num = @end_num + 10000

DROP TABLE posted_order_items_from_filemaker

END

INSERT INTO OrderItems (
OrderID,
ItemID,
Qty,
QtyDelivered,
QtyToSend,
item_price,
item_cost,
comments,
filemaker_discount,
filemaker_price_per_item_after_discount,
import_item,
filemaker_id
)
SELECT
F.order_key,
P.id,
F.quantity_received + F.quantity_to_ship,
F.quantity_received,
F.quantity_to_ship,
F.price_per_item
F.cost,
comments,
discount,
price_per_item_after_discount,
'1',
id
From dbo.posted_order_items_from_filemaker2 F
INNER JOIN dbo.Products P ON P.ProductCode = F.item_code

Steve Stout (12/10/2007)

By 'bad data' I mean data that is not in the correct format. For instance, in the above line item import, I ran across 3 rows which had '.5' for the quantity. As this is a float value in SQL and not an integer it made the cursor stop each time. I altered the original data to '1' and restarted the cursor. I am running into a lot of data that is blank/null when it should not be. Or has invalid values.

The data was pulled from a FileMaker Pro 4.0 database which seems to have extreemly simplistic data types. A 'Number' field can be a Integer or a Float for example. A Text field can be a varchar of any length. FileMaker does not make you specify the data lengths anywhere it seems, so I'm having to find out through trial and error where I need to tweak the lengths of my varchars.

If I'm processing this garbage data one row at a time and marking each row as 'done', when it stops I can find where it stopped fast and look at the data on that row to see what is happening.

I'll try implementing the suggestions above to see if I can allow for all the variables I'm dealing with. Thanks for the input! :)