SQL could be faster for sure, but it isn't as simple as a straight UPDATE command. A number of tables would require special handling to merge rows (like IM_ItemWarehouse, IM_ItemCustomerHistoryByPeriod...) or you can run into primary key violations. Oh, and PO_PurchasesHistory is a real table.
To get a full list of tables, below is a key word search for column names (where you have to know the system and manually check for alternative names like BillNo...).
SELECT c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%ItemCode%'
ORDER BY TableName
,ColumnName;
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------
Original Message:
Sent: 01-30-2023 10:45
From: Doug Higgs
Subject: Merge / Delete Items
That figures. You would think PVX could call a stored procedure that executes 100 times faster. This would be seemingly just a few lines of T-SQL.
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------
Original Message:
Sent: 01-30-2023 10:11
From: Phil McIntosh
Subject: Merge / Delete Items
Sage uses the same code across all editions, so the SQL ends up being RBAR (Row by agonizing row)
------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Original Message:
Sent: 01-30-2023 08:44
From: Doug Higgs
Subject: Merge / Delete Items
@Phil McIntosh I think the "history purge" is just a find and replace, or in SQL terms a select and update. I don;t understand why it's taking so long if the db is SQL. Is Sage using the business objects to select and update instead of calling a stored procedure that uses TSQL? Is that the reason. Seems to me a simple statement like below on each table would do it.UPDATE YourTableSET Column1 = REPLACE(Column1,'a','b')WHERE Column1 LIKE '%a%'
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
Original Message:
Sent: 01-30-2023 07:12
From: Phil McIntosh
Subject: Merge / Delete Items
@Doug Higgs - that was why my question of delete only. That only needs list of tables to delete records from. The history purge is trickier.
------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Original Message:
Sent: 01-30-2023 01:53
From: Doug Higgs
Subject: Merge / Delete Items
@Phil McIntosh If you could use your idea to do an actual delete then that would be great. Why should we care about beginning balances if the item is deleted? Beginning balance of what?
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
Original Message:
Sent: 01-29-2023 20:27
From: Phil McIntosh
Subject: Merge / Delete Items
If an MD can post a list of the tables that the utility hits for deletes, and what the "beginning balance" record is that gets created, it should be possible to at least do this for deletes. Do they want history purged for the items you are keeping?
Phil
------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Original Message:
Sent: 01-28-2023 14:37
From: Doug Higgs
Subject: Merge / Delete Items
@Andrew Hagenbach Did you find an alternate solution for this? I am doing one this weekend. It took 1 hour to complete the first 20. I have been asked to remove any item with a last receipt date prior to 1/1/2021. There are 140,000 items that meet this criteria. So, yes, we could be here a while. 3 minutes per item, so 420,000 minutes, or 7000 hours, or 292 days.... and this is premium. Should I ask the owner if they can hold of on invoicing and receipt of goods for 292 days until the merge completes?
------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles