Identity_insert 打开/关闭后 SQL Server 完整性检查
我正在将数据从遗留应用程序（php + mysql）导入到重写版本（sql server 2008 和 mvc3）。我目前正在编写数据库同步/转换包。
mysql数据有主键，但由于某种原因没有外键，并且因为数据仍然是相关的（尽管缺少外键），所以我的转换包正在使用IDENTITY_INSERT ON / OFF。因为源数据无法强制执行数据完整性，所以我想在执行插入并将 IDENTITY_INSERT 设置为 OFF 后执行完整性检查。
有没有好的方法来编写该操作的脚本？最好是在表级别而不是整个数据库？
谢谢。
I'm importing data from a legacy application (php + mysql) to a rewritten version (sql server 2008 and mvc3). I'm currently in the middle of writing a db synchronization / conversion package.
The mysql data has primary keys but no foreign keys for some reason and because the data is still relational (despite the lack of foreign keys) my conversion package is using IDENTITY_INSERT ON / OFF. Because the source data has no way of enforcing data integrity I would like to perform integrity check after I perform an insert and turn IDENTITY_INSERT to OFF.
Is there a good way to script that action? Preferably on a table level rather than whole db?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
就我个人而言，我会在导入新模式之前确定哪些数据不具有完整性。对每个子表（或者应该设计为子表的表）进行一个简单的 WHERE NOT Exists 查询就可以做到这一点。我不会导入无法绑定到父记录的数据。然而，我会创建一个异常表并将这些记录移入其中，并让一些用户看看他们是否可以找出父数据应该是什么。
Personally I would determine which data does not have integrity before importing into the new schema. A simple WHERE NOT Exists query on each child table (or what shoul have been ddesigned as a child table) will do that. I would not import data that cannot be tied to a parent record. I would however, create an exception table and move those records into it and have some of the users see if they can figure out what the parent data should be.