批量移动中更新和插入 (SQL Server)
我有一对数据库，一个是实时数据库，一个用于测试该实时数据库的配置。两者都驻留在同一服务器上。
我有三个表，用户（PK UserId、FK MainGroupId）、组（PK GroupId）和GroupMembers（PK GroupMemberId、FK GroupId 和 UserId）。
两个数据库上的表具有相同的架构，但是测试数据库有一组特殊的测试用户。组大多是稳定的，但有时我们会添加组，有时会更改组中的列数据。 GroupMembers 是相同的，但在测试数据库中指的是测试用户。
我需要能够实时更新组表，以编程方式测试用户。我想使用批量复制操作，但为此我必须先删除 Groups 表，这将导致约束冲突。
我可以将表批量复制到虚拟表，然后通过插入新行进行后期处理，并更新现有行。然而，我的问题是像Groups这样的表大约有30个，我不想将所有列名编码到UPDATE SET方法中的存储过程中。我也希望能够批量进行。
DBA 对授予 ALTER TABLE 权限以暂时删除约束持怀疑态度。
还有其他建议吗？
I have a pair of databases, one is a live database and one is for testing a configuration for that live database. Both reside on the same server.
I have three tables, Users (PK UserId, FK MainGroupId) and Groups (PK GroupId) and GroupMembers (PK GroupMemberId, FK GroupId and UserId).
The tables are the same schema on both databases however the test database has a set of special test users. Groups is mostly stable, but sometimes we add groups, and sometimes we change column data in the groups. GroupMembers is the same but in the test database refers to the test users.
I need to be able to update the Groups table from the live to test user programmatically. I want to use a bulk copy operation, but to do so I have to delete the Groups table first, which will cause a constraint violation.
I could copy the table in bulk to a dummy table, and then post process by doing an insert of the new rows, and update on the existing rows. However, my problems is that there are about 30 tables like Groups, and I don't want to encode all the column names into the stored procedure in the UPDATE SET method. I'd also like to be able to do it in bulk.
The DBAs are dubious about granting ALTER TABLE permission to temporarily drop the constraints.
Any other suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
既然两个数据库都在同一台服务器上，为什么不使用 MERGE 语句呢？
SInce both databases are on the same server, why not use a MERGE statement?
选择导出和导入。如果您按照正确的顺序进行操作，它应该可以正常工作。
select for export and import. If you do it in the right order it should work correctly.