与导入数据一起存储补充数据的策略 [英] Strategy for storing supplemental data alongside imported data

问题描述

我遇到这个问题几次，我想知道其他人在做什么。

I have ran into this problem a few times and I am wondering what other people are doing.

当我创建一个数据库，有时我必须导入数据定期进行表格，让我们说每天。我通常做的是删除所有的记录，并从外部数据源重新导入每个记录。

When I am creating a database, sometimes I have to import data into a table on a regular basis, let's say daily. What I normally do is delete all of the records and reimport every record from the external data source.

很多时候，我将需要存储一些更多的数据，记录，但不是来自原始导入源。通常这个额外数据来自用户输入。因此，我将创建另一个表，其主键与获取导入数据的表的键相匹配，并将此附加数据存储在新表中。如果这没有意义，这里是一个例子：

Many times I will have to store some more data that relates to the imported records, but doesn't come from the original import source. Typically this "extra data" comes from user input. So, I will create another table with a primary key matching the key of the table that gets the imported data, and store this additional data in the new table. If that doesn't make sense here is an example:

在旧的遗留系统中，我们存储员工数据。但是，我需要在无法连接到此旧系统的Web应用程序中使用此数据。因此，我创建一个数据库，其表与我在旧系统中的数据的模式匹配，并且我每天将每个记录导入到此表中。当我进行导入时，我删除每个记录并导入每个记录。

In an old legacy system, we store employee data. But, I need to use this data in a web application that can't connect to this old legacy system. So, I create a database with a table that matches the schema of the data that I have in the old system and I import each record into this table every day. When I do the import, I drop every record and import every record.

但在我的新系统中，员工可以保存一个生物。所以在另一个表中，我存储这个和他们的Id。

But in my new system, employees can save a bio. So in another table, I store this and their Id.

这将更简单只有一个表，但我不能这样做，因为我会丢弃数据

It would be simpler to have only one table, but I can't do that because I would drop data that doesn't exist elsewhere when I do the import.

另一个坏处是，因为我删除所有这些记录的导入，我不能定义外部关键约束与相关数据。

Another bad thing is that, because I am deleting all of these records for the import, I can't define foreign key constraints with related data.

我讨厌这样设计数据库，因为我知道有一个更好的方法。如果我可以在导入数据时进行更新而不是删除并导入所有数据，那不是很好吗？

I hate designing databases this way, because I know there is a better way. Wouldn't it be nice if I could do updates when I import the data, instead of deleting and importing it all?

我使用的是Sql server 2008，但我感兴趣听说可以与任何RDBMS协同工作的策略。

I am using Sql server 2008 but am interested to hear of strategies that can work with any RDBMS.

推荐答案

当你做导入，导入到临时表，然后更新生产表中的记录（通常意义上的更新：删除删除内容，添加新内容，修改更改内容）。

Well, when you do the import, import into a temp table, and then update the records in the production table (update in the generic sense of the word: delete what's deleted, add what's new, modify what's changed).

在2008年检查新的 MERGE SQL命令，它可能证明对这种情况非常有帮助。

You might also want to check out the new MERGE SQL command in 2008, it just might turn out to be very helpful for this case.

这篇关于与导入数据一起存储补充数据的策略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！