I have a batch system where the user enter suppliers invoices to a batch table (SupVchBat)
then selectively update certain invoice to outstanding invoice table (SupVch) and updated
suppliers (Supplier.sup_mtdpur) current balance and warehouse (warehouse.WHS_ MTD_SALE).
Such is done currently in the front end using VB, but the process is very slow hence I need
help to write batch update in back end using mssql. You help is highly appreciated.
Tables structure is as follows:
TABLE [SupVchBat] (
[vbat_whs_code] [nvarchar] (3),
[vbat_sup_code] [nvarchar] (4),
[vbat_tran_type] [smallint],
[vbat_vch_no] [nvarchar] (6),
[vbat_vch_date] [smalldatetime],
[vbat_sup_vch_no] [nvarchar] (10),
[vbat_sup_vch_date] [smalldatetime],
[vbat_val_gross] [money],
[vbat_val_disc] [money]
TABLE [SupVch] (
[SupVch_Code] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[vch_whs_code] [nvarchar] (3),
[vch_sup_code] [nvarchar] (4),
[vch_tran_type] [smallint],
[vch_no] [nvarchar] (6),
[vch_date] [smalldatetime],
[Vch_supvch_no] [nvarchar] (10),
[Vch_supvch_date] [smalldatetime],
[Vch_val_gross] [money],
[Vch_val_disc] [money]
TABLE [warehouse] (
[wh_code] [nvarchar] (3),
[wh_name] [nvarchar] (50),
[WHS_ MTD_SALE] [money],
[WHS_YTD_SALE] [money]
TABLE [Supplier] (
[Sup_code] [nvarchar] (4),
[sup_name] [nvarchar] (50),
[sup_mtdpur] [money],
[sup_ytdpur] [money]