将 SQL Server 表复制到另一个数据库时，如何最好地检查和转换 SQL Server 表中的每个单元格？
我有一系列表必须移动到另一个数据库。在移动它们的过程中，我需要检查每个单元格，如果它具有特定值，则将该单元格设为空。
我有一个包含元数据的表，其中的属性指向每个单元格，以及必须从原始表中删除的错误值列表。
例如，我有下表
TABLE1
ColumnA | ColumnB | ColumnC |
7 | 10 | DK |
83 | -7 | |
16 | 0 | True |
然后元表
Tablename | Column | Bad Value 1 | Bad Value 2 |
TABLE1 |ColumnC | DK | BOL |
TABLE1 |ColumnA | -99 | -1 |
TABLE1 |ColumnB | -1 | -7 |
在复制和转换的 TABLE1 中我想
TABLE1
ColumnA | ColumnB | ColumnC |
7 | 10 | NULL |
83 | NULL | |
16 | 0 | True |
了解什么是执行此操作的最佳方法。我考虑过编写一个为每个单元格调用的函数。它将传递在元“坏值”表中查找单元格所需的元信息以及单元格的当前值，然后返回单元格值或 NULL，具体取决于单元格是否与任何相应的匹配。糟糕的价值观。
如果我走这条路线，我可以在从原始表中读取值、将值写入新表或复制后运行一个单独的过程来更改复制表中的值时应用此方法。
我需要定期（每周）重复该过程。
您能提供的任何建议将不胜感激。
汤姆
I have a series of tables that I have to move to another database. In the process of moving them, I need to inspect every cell and if it has a specific value, null that cell.
I have table containing meta data with attributes which point to each cell, and a list of bad values that must be removed from the original tables.
For example I have the following table
TABLE1
ColumnA | ColumnB | ColumnC |
7 | 10 | DK |
83 | -7 | |
16 | 0 | True |
Then the meta table has
Tablename | Column | Bad Value 1 | Bad Value 2 |
TABLE1 |ColumnC | DK | BOL |
TABLE1 |ColumnA | -99 | -1 |
TABLE1 |ColumnB | -1 | -7 |
In the copied and transformed TABLE1 I would have
TABLE1
ColumnA | ColumnB | ColumnC |
7 | 10 | NULL |
83 | NULL | |
16 | 0 | True |
I want to understand what is going to be the best way to do this. I have considered writing a function that gets called for each cell. It would be passed the meta information needed to lookup the cell in the meta "bad values" table, and the current value of the cell, then would return the cell value or NULL, depending on whether or not the cell matched any of the corresponding bad values.
If I go this route, I could apply this at the point at which I read the value from the original table, write the value to the new table, or after copying, run a separate process that would change the value in the copied table.
I will need to repeat the process periodically (weekly).
Any advice you can provide would be greatly appreciated.
Tom
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要为此编写一个游标并使用动态 SQL。我认为您不会在这里找到任何人会为您编写整个代码，但这里有一些需要考虑的要点。
创建一个“主”表，其中包含要复制的所有表。我假设有可能在某个时候有一个不需要替换的表。这将使您能够轻松修改流程。建议的结构类似于
数据库、架构、表、活动，其中活动是您可以切换的。使用动态 SQL，为每个表构建一个
INSERT语句。从表中获取“不良”值以填充这些字段的CASE。因此，类似于CASE WHEN ColumnC = 'DK' OR ColumnC = 'BOL' THEN NULL ELSE ColumnC as ColumnC。这将更新“传输中”的值，因此您不会更改现有数据库，但您的目标会进行所需的更改。您需要为受影响表中的每一列提供一个条目，为每个表的列提供一个单独的“主”表，或者查询系统表以获取要复制的字段列表。如果您需要执行
CASE语句，则不能只执行SELECT *。You will need to write a cursor for this, and use Dynamic SQL. I don't think you'll find anyone here that will code the whole thing for you, but here are some points for consideration.
Make a "master" table that has all your tables to copy. I'm assuming it will be possible to have a table that doesn't need a substitution at some point. This will allow you to modify the process easily. Suggested structure would be something like
Database, Schema, Table, ActivewithActivebeing a bit you can toggle.Using Dynamic SQL, build an
INSERTstatement for each table. Get your "bad" values from the table to populate aCASEfor those fields. So, something likeCASE WHEN ColumnC = 'DK' OR ColumnC = 'BOL' THEN NULL ELSE ColumnC as ColumnC. This will update the value "in transit" so you don't alter your existing DB but your target has the desired changes.You will need to either have an entry for every column in the affected tables, have a separate "master" table for your columns per table, or query the system tables to get a list of fields to be copied. You can't just do a
SELECT *if you need to do aCASEstatement.