With old and new database of two sets of ufida old database (is no longer used, but there are 3 years of sales data not carried over to the new database) my way through LianZha 2 views of the data filtered and then writes the result to the oneself to create a table, a new database because the data is in constant change (likely query out sales data in the condition of not performed, the subsequent will continue to execute), that is to say, I by the time condition LianZha view to write their own tables, the results of subsequent data there will be a change needs to be updated to your table, please great god will guide how to LianZha update to the result of the data in the table

CodePudding user response:

Use the Merge Into function, if there is update, there is no insert

CodePudding user response:

Refer to the article
https://www.cnblogs.com/lgx5/p/4813357.html

CodePudding user response:

The MERGE INTO Report_temp. Dbo. Sales data source as s
USING (SELECT dcreatesystime as order date,
Csocode as order number,
Irowno as order line number,
Ccusabbname as customer name,
Cpersonname as salesman,
Cdepname as sales department,
Cstname as sales type,
Cinvcode as inventory coding,
Cinvstd as sales material,
Cfree1 as width,
Cfree2 as thickness,
Itaxunitprice as tax monovalent,
Combined isum as tax,
Iquantity as the order quantity,
Ifhquantity as delivery quantity,
Cdefine23 as packing method,
Cdefine24 as the zinc layer,
Cdefine25 as surface treatment,
Cdefine29 as use
The FROM SaleOrderQ left join SaleOrderSQ ON SaleOrderQ. Id=SaleOrderSQ. Id
WHERE dcreatesystime between '2019-9-1' and '2020-6-30') AS ss ON s. order date=ss. The order date and s. order line number=ss. Order line number
The WHEN MATCHED
THEN UPDATE the SET s. order date=ss. Order date,
S. order number=ss. The order number,
S. order line number=ss. Order line number,
S. customer name=ss. The customer name,
S. salesman=ss. Salesman,
S.=ss. The Marketing Department, Marketing Department
S. sales type=ss. Sales type,
S. stock code=ss inventory coding,
S. sales=ss material. Sales material,
S. width=ss. Width,
S. thickness=ss. Thickness,
S. tax price=ss. Tax monovalent,
S. tax total=ss. Tax combined,
S.=ss orders. The order quantity,
S. delivery number=ss. Delivery quantity,
S. packing=ss. The packing way,
S. zinc layer=ss. The zinc layer,
S.=ss. The surface treatment, surface treatment,
S. use=ss. Use the
WHEN NOT MATCHED
THEN INSERT VALUES (ss) order date,
Ss. The order number,
Ss. Order line number,
Ss. The customer name,
Ss. The clerk,
Ss. The sales department,
Ss. Sales type,
Ss. Inventory coding,
Ss. Sales material,
Ss. Width,
Ss. Thickness,
Ss. Tax monovalent,
Ss. Tax combined,
Ss. The order quantity,
Ss. Delivery quantity,
Ss. The packing way,
Ss. The zinc layer,
Ss. Surface treatment,
Ss.)
The OUTPUT $ACTION AS [ACTION], inserted. *, does. * into Report_temp dbo. Sales data synchronization log;

Here ouput sales data synchronization log into Report_temp database table will be prompted to invalid under can also don't know how to write, please advice?
I want to deposit the logging operation on a table as a log

CodePudding user response:

What is error message? Can post and see; Sales data synchronization log table structure?
You here if only insert operations, it does. * it is always empty, but. Sales data synchronization log column must have two sets of
https://blog.csdn.net/tjvictor/article/details/4344949

CodePudding user response:

Is the OUTPUT $ACTION AS [ACTION], inserted. *, does. * into Report_temp dbo. Sales data synchronization log;
Report_temp. Dbo. Sales data synchronization log here are the red tip is invalid