将数据从未知结构的数据库传输到已知结构的数据库
我有一些挑战需要帮助。我需要从任意来源将数据拉入我的 SQL 数据库。 详细信息是：我知道我的数据库的确切结构，并且该结构不会改变。当我确实接收新数据时，它只会发生一次，即在我设置数据库实例时。我将为我的数据库创建许多实例，每次它都必须从不同的源提取数据，并且这些源将以不同的方式构建。 该数据很可能包含数千行记录。数据源很可能保存在 Excel、Access 中，更罕见的是 Word，甚至更罕见的是，它将保存在 SQL 数据库中。 我可以假设大部分核心数据都是相同的，只是放在不同的位置。不管情况如何，他们都会遵循一个总体分组。 本质上，我正在将数据从遗留系统传输到 SQL 系统，这必须为许多组完成，并且他们需要自己的私有数据库实例。 关于我将如何做到这一点有什么想法吗？编写一个程序来为我完成大部分工作有多难？
I have a few challenges I need help on. I need to pull data in to my SQL database from arbitrary sources.
The details are: I know the exact structure of my database and the structure will not change. When I do take in new data, it will occur only one time, at the time I set up an instance of my database. I will make many instances of my database and each time it will have to pull data from a different source, and those sources will be structured in different ways.
The data will most likely contain thousands of rows of records. The data source will most likely be held in Excel, Access, more rare Word and even rarer, it'll be held in a SQL database.
I can assume that most of the core data will be the same, just put in different locations. They will follow a general grouping despite how there held.
Essentially, I'm transferring data from legacy systems to a SQL system and this must be done for many groups and they need their own private instance of the database.
Any thoughts on how I would do this? How hard would it be to write a program that would do most of this for me?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这绝对是一个现实世界的问题。是否可以编写一个程序来完成大部分工作？我想，这不是大部分，但也许是其中一部分。
对于目标系统中的每个表，创建一个视图来显示您期望能够插入的源数据。选择易于区分需要做什么的列名称；您很可能会选择与
INSERT语句中的目标列相匹配的列名称。将INSERT语句保存为存储过程。现在，当您获得新格式的新数据源时，您仍然需要重新创建视图，但是一旦视图在您选择的列名称下显示正确的数据，您就可以运行存储过程而不进行任何更改。
This is definitely a real-world question. Is it possible to write a program that will do most of this? Not most of this, I think, but perhaps some of it.
For each table in your target system, create a view that displays the source data you expect to be able to insert. Choose column names that make it easy to tell what has to be done; most likely you'll choose column names that match the target columns in your
INSERTstatement. Save yourINSERTstatements as stored procedures.Now, when you are given a new source of data in a new format, you will still have to recreate your views, but once the views are displaying the right data under your chosen column names, you can run your stored procedures without change.
我有一个类似类型的项目，其中从 Access、.ini 文件、文件修改日期和 MySql 检索数据。我每天早上都会抓取这些数据，并基本上附加到一组 SqlServer 架构中。
我创建了一个 DataTable，并在迭代一组目录时将数据插入到每个新行中。完成数据表后，我执行批量复制以附加到数据库。
我希望这对你有一点帮助。我知道我的项目并没有涵盖您问题的所有方面；但也没有 DBA 提供视图、存储过程等。我也没有额外的时间投入到这些事情上。虽然不是最有利的条件，但事实就是如此。
哈...
I have a similar type of project where data is being retrieved from Access, .ini file, file modification dates, and MySql. I scrape this data every morning and basically append to a set SqlServer schema.
I created a DataTable and as I iterate a set of directories, insert the data into each new row. Once I have the DataTable complete, I perform a bulkcopy to append to the database.
I hope that helps you out a bit. I know my project doesn't cover all the aspects of your question; but also don't have a DBA to provide views, stored procedures, etc. Nor do I have the additional time to devote to such things. Not the most favorable of conditions, but that's the way it is.
HTH...
解决此问题的最佳方法是使用 ETL（提取-转换-加载）解决方案。一个不错的选择是 SSIS，它是通过 Microsoft 的 BI 套件实现的。
The best way of solving this problem is with and ETL (Extract-Transform-Load) solution. A good choice is SSIS which is through Microsoft's BI suite.
这是意识的构建块或基础......
1 组织数千个类似于 DNA 的文件的数据库，
2 用户界面
3 个部分被隐藏，防止系统破坏/崩溃
This is the building blocks for consciousness or the base......
1 A data base that organizes thousands of files similar to dna,
2 user interface
3 parts are hidden, preventing a system breach/crash