Hi, here is the scenario.
I need to update an inventory table.
The inventory table is like this:
Table M_Inventory
MI_Id int (key) incremental
MI_Sku varchar (8)
CAU_Location (foreign key from a table named CA_Location)
Other no relevant columns
There are 2000 SKUs for each location (3 of them) for 6,000 records and growing.
Now, there is a Ticket table, their relevant information for this operation is:
Table M_Detail_Ticket
MT_Id (Ticket number, foreign key from a table named M_Ticket)
SKU varchar (8)
MDT_Quantity
Other columns.
On average a ticket will have about 10 SKUs in it.
There is a stored procedure to "close" the ticket. This means change some flags on the M_Ticket, move total to a sales table and other stuff, this is all done and working fine. Easy to perform because the store procedure performs single record operations.
I am facing problem getting a solution for this multiple record update.
Your suggestion are appreciated.
Can I do this task on a single update statemnt?
No much experience on indexes outside primary key, can I implement a sku/location index in order to improve performance?
Thank you in advance.
I need to update an inventory table.
The inventory table is like this:
Table M_Inventory
MI_Id int (key) incremental
MI_Sku varchar (8)
CAU_Location (foreign key from a table named CA_Location)
Other no relevant columns
There are 2000 SKUs for each location (3 of them) for 6,000 records and growing.
Now, there is a Ticket table, their relevant information for this operation is:
Table M_Detail_Ticket
MT_Id (Ticket number, foreign key from a table named M_Ticket)
SKU varchar (8)
MDT_Quantity
Other columns.
On average a ticket will have about 10 SKUs in it.
There is a stored procedure to "close" the ticket. This means change some flags on the M_Ticket, move total to a sales table and other stuff, this is all done and working fine. Easy to perform because the store procedure performs single record operations.
I am facing problem getting a solution for this multiple record update.
Your suggestion are appreciated.
Can I do this task on a single update statemnt?
No much experience on indexes outside primary key, can I implement a sku/location index in order to improve performance?
Thank you in advance.