We have a database in MS SQL Server 2005 (actually development database), which hosts many relational tables holding working space of an application. Using this application user can define many new data structures each are stored as rows in different tables and a column named 'finalised' of type bit indicates that the set of data copied to another database (which acts a release db with only finalised data) which exactly has the same schema with development database.
我们在MS SQL Server 2005(实际上是开发数据库)中有一个数据库,它托管许多保存应用程序工作空间的关系表。使用此应用程序,用户可以定义许多新的数据结构,每个数据结构作为行存储在不同的表中,名为“finalized”的bit类型的列表示复制到另一个数据库的数据集(其作用只有最终数据的发布数据库)与开发数据库完全相同的模式。
As the user finishes his work, he starts a synchronisation process for a set of data (whose finalised field is not set to 1 yet) and after performing a few validation rules, if everything seems to OK, we will copy this set to release database and mark finalised field of related tables as 1 in the development database. Is there a simple mechanism that we can use rather than selecting data from the first db and inserting it to destination programmatically? There are too many tables to select from and these tables actually holds relational data which are connected on id values.
当用户完成他的工作时,他启动一组数据的同步过程(其最终字段尚未设置为1),并且在执行一些验证规则后,如果一切正常,我们将此集复制到发布数据库并在开发数据库中将相关表的最终字段标记为1。是否有一个简单的机制,我们可以使用而不是从第一个数据库中选择数据并以编程方式将其插入目标?有太多的表可供选择,这些表实际上包含在id值上连接的关系数据。
The release db mostly seems to have views of tables from source database with condition 'where finalised = 1', but we should also add a set when the user starts synchronisation for a data set, while the finalised field is still 0 (if the validation process is OK).
发布数据库似乎主要有来自源数据库的表的视图,条件为'where finalized = 1',但我们还应该在用户启动数据集同步时添加一个集合,而最终的字段仍为0(如果验证)过程没问题)。
Thanks in advance
提前致谢
1 个解决方案
#1
0
A SQL script (stored proc) to promote these new table entries is likely your best option in this scenario.
在这种情况下,用于提升这些新表条目的SQL脚本(存储过程)可能是您的最佳选择。
Some alternatives would be:
一些替代方案是:
Inserting the dev identities into production tables -- which is risky if data can be inserted via any other method. It's also worth considering table locks that occur when identity inserts are enabled -- your end users would likely be unable to query the production tables while this is running. This can be done with the syntax:
将dev标识插入生产表 - 如果可以通过任何其他方法插入数据,则存在风险。同样值得考虑启用标识插入时发生的表锁 - 您的最终用户可能无法在生成表时查询生产表。这可以使用以下语法完成:
SET IDENTITY_INSERT [myTABLE] ON
INSERT INTO PROD.DBO.myTABLE (COL1,COL2,COL3)
SELECT COL1,COL2,COL3 FROM DEV.DBO.myTABLE WHERE...
SET IDENTITY_INSERT [myTABLE] OFF
Here is a KB on this. http://technet.microsoft.com/en-us/library/ms188059.aspx
这是一个KB。 http://technet.microsoft.com/en-us/library/ms188059.aspx
Another option would be filtered replication from dev to prod -- again, this would only work if there is no other means of inserting the data into production, and also holds additional risk factors if the dev entries can be altered after they are flagged as approved - as the changes would immediately replicate to production, potentially impacting end users. Your production tables would NOT be modifiable in this instance.
另一种选择是从dev到prod的过滤复制 - 再次,这只有在没有其他方法将数据插入生产时才有效,并且如果dev条目在被标记为已批准后可以更改,则还有其他风险因素 - 因为更改会立即复制到生产中,可能会影响最终用户。在这种情况下,您的生产表不可修改。
Here is a KB on filtered replication: http://technet.microsoft.com/en-us/library/ms146925(v=sql.105).aspx
以下是过滤复制的KB:http://technet.microsoft.com/en-us/library/ms146925(v = sql.105).aspx
Ideally, a production environment is isolated completely from a development environment -- which means the options I just suggested would NOT be used -- but I presented them as FYIs.
理想情况下,生产环境完全与开发环境隔离 - 这意味着我刚才建议的选项不会被使用 - 但我将它们表示为FYI。
That said, assuming your table schema's are identical, you could look at adding a new identity column in production, thus allowing you to insert the dev id along side it.
也就是说,假设您的表模式是相同的,您可以考虑在生产中添加新的标识列,从而允许您在其旁边插入dev id。
ie:
dev.dbo.myTable
id int identity(1,1) primary key,
val1 int,
val2 int,
flag bit
prod.dbo.myTable
newid int identity(1,1) primary key, -- new column in prod
id int,
val1 int,
val2 int,
flag bit
You would need to add a new identity to each production table, so every production table would hold the dev id... and your front end code would remain unchanged. This would be my approach if schema changes were an option.
您需要为每个生产表添加一个新标识,因此每个生产表都将保留开发标识...并且您的前端代码将保持不变。如果架构更改是一个选项,这将是我的方法。
This would allow you to do a straight insert from dev to prod, keeping the dev id's intact, and also providing a method of matching prod back to dev at the same time.
这将允许您从dev到prod直接插入,保持dev id的完整性,并且还提供了一种将prod同时匹配到dev的方法。
Hope this helps.
希望这可以帮助。
#1
0
A SQL script (stored proc) to promote these new table entries is likely your best option in this scenario.
在这种情况下,用于提升这些新表条目的SQL脚本(存储过程)可能是您的最佳选择。
Some alternatives would be:
一些替代方案是:
Inserting the dev identities into production tables -- which is risky if data can be inserted via any other method. It's also worth considering table locks that occur when identity inserts are enabled -- your end users would likely be unable to query the production tables while this is running. This can be done with the syntax:
将dev标识插入生产表 - 如果可以通过任何其他方法插入数据,则存在风险。同样值得考虑启用标识插入时发生的表锁 - 您的最终用户可能无法在生成表时查询生产表。这可以使用以下语法完成:
SET IDENTITY_INSERT [myTABLE] ON
INSERT INTO PROD.DBO.myTABLE (COL1,COL2,COL3)
SELECT COL1,COL2,COL3 FROM DEV.DBO.myTABLE WHERE...
SET IDENTITY_INSERT [myTABLE] OFF
Here is a KB on this. http://technet.microsoft.com/en-us/library/ms188059.aspx
这是一个KB。 http://technet.microsoft.com/en-us/library/ms188059.aspx
Another option would be filtered replication from dev to prod -- again, this would only work if there is no other means of inserting the data into production, and also holds additional risk factors if the dev entries can be altered after they are flagged as approved - as the changes would immediately replicate to production, potentially impacting end users. Your production tables would NOT be modifiable in this instance.
另一种选择是从dev到prod的过滤复制 - 再次,这只有在没有其他方法将数据插入生产时才有效,并且如果dev条目在被标记为已批准后可以更改,则还有其他风险因素 - 因为更改会立即复制到生产中,可能会影响最终用户。在这种情况下,您的生产表不可修改。
Here is a KB on filtered replication: http://technet.microsoft.com/en-us/library/ms146925(v=sql.105).aspx
以下是过滤复制的KB:http://technet.microsoft.com/en-us/library/ms146925(v = sql.105).aspx
Ideally, a production environment is isolated completely from a development environment -- which means the options I just suggested would NOT be used -- but I presented them as FYIs.
理想情况下,生产环境完全与开发环境隔离 - 这意味着我刚才建议的选项不会被使用 - 但我将它们表示为FYI。
That said, assuming your table schema's are identical, you could look at adding a new identity column in production, thus allowing you to insert the dev id along side it.
也就是说,假设您的表模式是相同的,您可以考虑在生产中添加新的标识列,从而允许您在其旁边插入dev id。
ie:
dev.dbo.myTable
id int identity(1,1) primary key,
val1 int,
val2 int,
flag bit
prod.dbo.myTable
newid int identity(1,1) primary key, -- new column in prod
id int,
val1 int,
val2 int,
flag bit
You would need to add a new identity to each production table, so every production table would hold the dev id... and your front end code would remain unchanged. This would be my approach if schema changes were an option.
您需要为每个生产表添加一个新标识,因此每个生产表都将保留开发标识...并且您的前端代码将保持不变。如果架构更改是一个选项,这将是我的方法。
This would allow you to do a straight insert from dev to prod, keeping the dev id's intact, and also providing a method of matching prod back to dev at the same time.
这将允许您从dev到prod直接插入,保持dev id的完整性,并且还提供了一种将prod同时匹配到dev的方法。
Hope this helps.
希望这可以帮助。