将每日数据添加到数据库的推荐方式
我每天都会收到新的数据文件。现在，我正在构建包含所有必需表的数据库，以导入数据并执行所需的计算。
我应该将每一天的数据附加到我当前的表中吗？每个文件都包含一个日期列，如果我需要分析某一特定日期的数据，将来可以进行“WHERE”查询。或者我应该每天创建一组新的表格？
我是数据库设计新手（来自 Excel）。我将为此使用 SQL Server。
I receive new data files every day. Right now, I'm building the database with all the required tables to import the data and perform the required calculations.
Should I just append each new day's data to my current tables? Each file contains a date column, which would allow for a "WHERE" query in the future if I need to analyze data for one particular day. Or should I be creating a new set of tables for every day?
I'm new to database design (coming from Excel). I will be using SQL Server for this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
假设接收到的数据的结构是相同的，您应该只需要一组表，而不是每天创建新表。
我建议将传入数据中的日期列的值存储在数据库中，并在表中添加“CreateDate”列，默认值为“GetDate()”，以便自动填充当前日期插入行的日期。
您可能还需要另一列来存储导入行的数据文件名，但如果您已经存储了日期列的值和插入行的日期，则实际上没有必要这样做。
过去，当使用自定义数据加载应用程序执行此类活动时，我还发现创建日志文件来记录成功/错误/警告消息（包括源数据和目标数据库的某种类型的唯一键）很有用- IE。如果来自 Excel 文件并进入数据库列，您可以存储 Excel 中的行索引和插入行的主键。这有助于稍后追踪任何问题。
Assuming that the structure of the data being received is the same, you should only need one set of tables rather than creating new tables each day.
I'd recommend storing the value of the date column from your incoming data in your database, and also having a 'CreateDate' column in your tables, with a default value of 'GetDate()' so that it automatically gets populated with the current date when the row is inserted.
You may also want to have another column to store the data filename that the row was imported from, but if you're already storing the value of the date column and the date that the row was inserted, this shouldn't really be necessary.
In the past, when doing this type of activity using a custom data loader application, I've also found it useful to create log files to log success/error/warning messages, including some type of unique key of the source data and target database - ie. if coming from an Excel file and going into a database column, you could store the row index from Excel and the primary key of the inserted row. This helps tracking down any problems later on.
您可能需要考虑查看SSIS（SqlServer集成服务） 。它是用于执行 ETL 活动的 SqlServer 工具。
You might want to consider having a look at SSIS (SqlServer Integration Services). It's the SqlServer tool for doing ETL activities.
是的，将每天的数据附加到表格中；一组包含所有数据的表格。
是的，使用日期列来标识数据加载的日期。
也许还有另一个带有日期列和 clob 列的表。包含加载日期的日期和包含您导入的文件的 clob。
yes, append each day's data to the tables; 1 set of tables for all data.
yes, use a date column to identify the day that the data was loaded.
maybe have another table with a date column and a clob column. The date to contain the load date and the clob to contain the file that you imported.
好问题。您绝对应该拥有一组表并每天附加数据。考虑一下：如果每天创建一组新表，那么每月报告查询会是什么样子？季报查询？这会很混乱，
UNION和JOIN到处都是。一组带有 WHERE 子句的表使查询和报告变得易于管理。
您可能会阅读一些有关关系数据库理论的内容。 维基百科是一个很好的起点。如果您有技巧的话，基础知识非常简单。
Good question. You most definitely should have a single set of tables and append the data daily. Consider this: if you create a new set of tables each day, what would, say, a monthly report query look like? A quarterly report query? It would be a mess, with
UNIONsandJOINsall over the place.A single set of tables with a WHERE clause makes the querying and reporting manageable.
You might do a little reading on relational database theory. Wikipedia is a good place to start. The basics are pretty straightforward if you have the knack for it.
不管怎样，我都会将数据加载到阶段表中，然后附加到主表中。然后，我会每周刷新一次主表中的所有数据，以确保数据按照源保持正确。
马库斯
I would have the data load into a stage table regardless and append to the main tables after. Once a week i would then refresh all data in the main table to ensure that the data remains correct as per the source.
Marcus