815
We have approximately 12,000 items in our database that do not have a supplier assigned to them. Is there a way to assign the supplier info to these without doing them one at a time? We have 300 suppliers!
*This post is locked for comments
Assigning suppliers to a bulk of items involves an update statement to the Item table and an Insert statement to the SupplierList table, the 2 statements would be similar to this: -
***************
update item
set supplierid=supplier.id
from supplier
where supplier.code='xxxx'
and ItemLookupCode in ('','','')
and SupplierID=0
INSERT INTO SupplierList
SELECT DISTINCT '1' as MinimumOrder,Item.ID as ItemID, SupplierID, Cost,'0' as ReorderNumber
,'1' as MasterPackQuantity,NULL as DBTimeStamp,'0' as TaxRate
FROM Item
WHERE ItemLookupCode in ('','','')
**************
The above statements assume that your criteria is based on the ItemLookupCode, while is definitely not practical to execute the query 300 times specifying the items for each supplier
So my suggestion is that you prepare an excel sheet with 2 columns only (ItemLookupcode & supplier code) then import this excel sheet into a temp table in the database, then I can change the above query to use the temp table you've just imported so that you execute the query only once
If the above suggestion is OK with you, just let me know and I will post the edited query. My only concern is that you have to be sure that all of the items in the excel sheet don't have any suppliers already assigned to them, although we can check this anyway before executing the query
A Hassan
RMS Leaders
Hello Krista,
It would be possible, but you would have to write SQL scripts. As you have so many items and so many suppliers it would be pretty time consuming. You could open a support request, but i would imagine it would end up being an Advisory service.
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
