我需要从一个 SQL Server 数据库更新另一个数据库中的客户信息
我是数据库新手，我正在寻求一些帮助。我们编写了一个自定义应用程序，将其数据保存在 SQL Server 数据库中。我们还有一个基于 QuickBooks 销售点的 POS 系统。我们正在购买一款名为 QODBC 的产品，它为我们提供了一个符合 ODBS 的 QBPOS 接口。
SQL Server 数据库有一个名为 customerinfo 的表，共有 70 列信息，其中有 15 列是我们感兴趣的。
它们是：
id、txtfname、txtlname、txtemployer、txtphone、电子邮件、txtaddress、txtcity、txtstate、txtzip、IDENTIFICATIONType、IDENTIFICATIONNumber、IDState、IDENTIFICATIONExpiry 和 IDENTIFICATIONExpiry。 dtp出生。
这些列需要导入到 ODBC 可访问的 QBPOS 中，该 QBPOS 的列名称不同，QB 位于上述所有名称之前（例如 SQL Server 列是 id，因此 QBPOS 列是 QBid）。 我们想要做
的是定期（例如每分钟左右）将我们需要的数据导入 QBPOS。首先，除了每分钟左右的导入计划之外，是否可以通过我们可以放在桌面上并且仅在需要时执行的图标来完成此操作？另外，我们是否能够为现有客户更新 SQL Server 数据库中更改的数据？
我预先感谢大家提供的任何帮助！
I am new to databases, and I am looking for some help. We have a custom app that was written that keeps its data in a SQL Server database. We also have a POS system which is based upon QuickBooks Point Of Sale. We are purchasing a product called QODBC, which gives us an ODBS compliant interface to QBPOS.
The SQL Server database has a table called customerinfo and has 15 columns of information we are interested in out of 70 total.
They are:
id, txtfname, txtlname, txtemployer, txtphone, email, txtaddress, txtcity, txtstate, txtzip, IDENTIFICATIONType, IDENTIFICATIONumber, IDState, IDENTIFICATIONExpiry & dtpBirth.
These columns need to be imported into the ODBC-accessable QBPOS, which has columns that are named differently, with QB preceeding all the above names (example the SQL Server column is id so the QBPOS column is QBid). What
we would like to do is import on a regular schedule (say every minute or so) the data that we need into QBPOS. First, other than the every minute or so import schedule, is it possible to do it via an icon we could just put on the desktop and only do it when we need to? Also, would we be able to just update changed data from the SQL Server database for existing customers?
I thank you all in advance for any help you can offer!!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一般来说，拉取数据比推送数据要简单得多。
这意味着您最好在 QBPOS 系统上设置一个计划，查询 MS SQL Server 来收集您需要的数据。然而，我对 QBPos 一无所知，也无法评论如何做到这一点，甚至无法评论是否可能。
另一种方法是从 SQL Server 一次将一条记录推送到 QBPOS。这当然是可能的，但我希望它会很慢...
（虽然您的代码可能看起来像是一次性推送一整套数据，但如果您分析 SQL Server，您会发现它正在触发许多单独的插入/更新/删除命令。这就是为什么它速度较慢的原因两者的机制。）
要仅使用 SQL Server 的更改来更新 QBPOS 服务器，您需要执行以下操作...
- 记录每次更改发生的时间（删除以及插入和更新）
- 在 QBPOS 服务器中存储上次收到的更改的时间戳
- 检查 SQL Server 存储过程中的值以确定要推送哪些更改
注意：“已推送的内容”和“已接收的内容”之间存在差异，因为 QBPOS 数据库可以从后台恢复 -这意味着“最后收到的数据”值必须存储在 QBPOS 数据库中。
这个答案故意设置得相当高，因为编写所有这些问题的精确解决方案的脚本将占用大量空间。如果有任何您不熟悉的术语或概念，我建议您在在线图书或 Google 中搜索它们，看看您可以自己管理哪些内容，并尝试一下。在这类事情中，了解自己在做什么以及为什么这样做非常重要，而不是仅仅复制别人的明确指示。
As a general rule, it is much simpler for data to be pulled rather than pushed.
This means that it is preferable for you to set up a schedule on the QBPOS system, that queries the MS SQL Server to collect the data you need. I do not, however, know anything about QBPos and can't comment on how to do this, or even if it is possible.
The alternative is to push one record at a time into QBPOS from SQL Server. This is certainly possible, but I would expect it to be slow...
(Although your code may look like it's pushing a whole set of data in one go, if you profile the SQL Server you'd see that it's firing off many individual Insert/Update/Delete commands. This is why it is the slower mechanism of the two.)
To update the QBPOS server with just the changes to the SQL Server, you need to do something like...
- Record when each change happened (deletes as well as inserts and updates)
- Store in the QBPOS server the timestamp of the last change it received
- Check that value in your SQL Server stored procedure to determine what changes to push
Note: There is a difference between "what has been pushed" and "what has been received" as it is possible for the QBPOS database to be restored from back-up, etc. This means that the "last received data" value must be stored in the QBPOS database.
This answer is deliberately fairly high level as scripting the exact solution to all of this would take a whole lot of space. If there are any terms or concepts you're unfamiliar with, I'd recommend searching them out in Books Online or Google, and seeing what you can manage yourself, and having a little bit of a play. It's very important in these kinds of things to know what your doing and why, rather than just copy explicit instructions from some-one else.