将登台表中的数据加载到多个表中 [英] Loading data from staging table into multiple tables

问题描述

我正在使用MS SQL server 2017，这是一个测试数据库，我正在尝试解决问题。



-此数据库包含4个表格（日历，客户，销售，Stage_Orders）

-Stage_Orders是一个包含新订单的临时表。

-我需要使用这些新数据加载Customers和Sales表。

- 如果客户（由StageNrders表中的CustomerNumber标识）已存在于Customers表中，则不会再次添加，但是，如果任何属性已从已存在的记录中更改在Customers表中，应该更新它们。如果客户表中不存在客户，则应添加该客户。

- 应为所创建的任何新客户记录生成代理密钥。

-销售表应该从Stage_Orders加载销售交易，并且应该查找customerskey并将其分配给Sales表中的新行，以便可以在查询中将其加入CustomerKey上的Customers表。



我尝试了什么：



我尝试过一个MERGE语句，插入新的rowif客户端没有当地址，电话号码等不同时，或更新特定列。



我似乎无法正确使用。

I'm using MS SQL server 2017 and this is a test database where I'm trying to solve a problem.

-This database contains 4 tables (Calendar, Customers, Sales, Stage_Orders)
-Stage_Orders is a staging table that contains new orders.
-I need to load the Customers and Sales table with this new data.
-If the Customer, identified by the CustomerNumber from the Stage_Orders table already exists in the Customers table then it is not added again, however, if any of the attributes have changed from the record that already exists in the Customers table, they should be updated. If the customer does not exist in the Customers table it should be added.
-A surrogate key should be generated for any new Customer records created.
-The Sales table should be loaded with the sales transactions from the Stage_Orders and the customerskey should be looked up and assigned to the new row in the Sales table so that it can be joined in a query to the Customers table on CustomerKey.

What I have tried:

I've tried a MERGE statement that inserts new rowif the customerid doesn't, or updates specific columns when the address, phone number, etc. is different.

I can't seem to get it right.

推荐答案

由于你没有发布任何SQL只能进行通用讨论...



听起来像是MERGE结构本身会给你带来麻烦，也许你在一次运行中试图做太多。如果将数据分成两个不同的步骤，可能会更容易。



首先，加载目标中不存在的所有行（执行INSERT）。在此查询中，您可以处理所有插入特定要求，而无需担心更新。插入部分完成后，使用另一个查询再次使用更新特定定义进行更新。



当然，分离操作会导致源表中的两个不同的运行但是如果插入和更新之间的逻辑不同，尝试将它全部放在一个语句中可能会变得很复杂。



两个操作单独成功完成后，您可以随时调查将两个语句都包含在单个MERGE中是否合理。

Since you've posted no SQL to work with only generic discussion is possible...

It sounds like the MERGE structure itself is causing you trouble, perhaps you're trying to do too much in a single run. It might be easier if you separate loading the data into two different steps.

First, load all rows that do not exist in the target (do the INSERTs). In this query you can handle all insert specific requirements without having to worry about updates. After the insert portion is done, do the updates with another query, again with update specific definitions.

Of course separating the operations leads to two different runs from the source table but if the logic between inserts and updates are different, it might get complex to try to put it all in a single statement.

After both operations are successfully done separately, you can always investigate if it's reasonable to include both statements into a single MERGE.

这篇关于将登台表中的数据加载到多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！