Similar problem here...3.98, trying to update product records but mine fails with "Product import failedIndex was outside the bounds of the array". Downloaded sample import table from forum and replaced the records with one row of data but fails with same error. I have about 11,000 products that I need to replace images. This is a big problem...

Wait... I just re-read the post (although I'd still do it in SSMS).... are you trying to overwrite products with updated information? Because the import (i believe) will create additional records, and not replace or update your existing records. It could be the error that you're running into is that it's trying to do an INSERT with the same product ID and so forth, and that would be bad.

Then how can I update products? What if I merely have to update quantities, which I will have to do on a very regular basis?

I update my QOH daily on my cart. It's quite simple, actually.

In fact, I get my current QOH from MAS90, and update my smith cart that way. I can post the routine (although you'll have to modify it, it's obviously written to work with MAS).

Would you like to see the way I do it? It's just an UPDATE statement....

For me QOH actually isn't a bid deal (it'd be a nice feature to check unlimited quantity)

My problem is that we have 3 quantity breaks with price adjustments, and I'd like to just put them in a spreadsheet and upload. Do you do an update statement and just import a file (csv, something else) into excel? I'd be curious to see your update routine yes, I'd just change the table it updates and fields; a little more work on my end but rather than using the GUI to update thousands of product quantity breaks and prices yes, I'm open to that :-)

Well, here is the routine I run nightly. I'll explain it at the end of the post....

SELECT ItemNumber, (QtyOnHand - QtyOnSalesOrder) AS QOH
INTO #PatchInventoryCounts
FROM
OPENQUERY(MAS90,'SELECT ItemNumber, QtyOnHand, QtyOnSalesOrder
FROM IM2_InventoryItemWhseDetl
WHERE ItemNumber LIKE ''S-%''
OR ItemNumber LIKE ''P%''
OR ItemNumber=''PIN001''
OR ItemNumber LIKE ''BP%''')


UPDATE dbo.Smith_Products
SET QuantityOnHand = #PatchInventoryCounts.QOH
FROM #PatchInventoryCounts
WHERE ModelNumber = #PatchInventoryCounts.ItemNumber

... OK, what it's doing is getting the Item Number and the QOH less the Quantity on Sales order from MAS90, and putting that information into a temporary table. THe OPENQUERY is used because the MAS90 database is a linked server. You *COULD* use this same method to write a stored procedure to open an Excel document instead.

The next portion updates the Smith_Products table, setting the QuantityOnHand from the information in the temporary table that was built.

I suppose that in retrospect I *could* have done this whole thing without the temporary table, however sometimes the connection to MAS can be a little flaky, and I wanted to make sure that step 1 finished before step 2 executed.

I'm digging a little further into how to do this directly from an Excel file; perhaps two SP's... one to create the excel sheet, and one to update the Smith_Products table from the excel sheet when you were through making changes.

If you'd like to go that route, it sounds like a fun little challenge to me... I could write that and post the two SP's here for you.

As you can see, UPDATE statements in SQL are very straightforward, and very simple to do. Easier than inserts, even.

I don't have a comment on the quote from the manual; obviously I didn't write the manual, but I certainly agree with your interpretation of the quote, so I retract my statement about the routine not being "broken".

--Greg

Hmmm.... I haven't forgotten about this. I'm just having a little problem opening an excel document directly in SSMS without making it a "linked server".