How to best inspect and transform every cell in a sql server table when copying it to another database? -

i have series of tables have move database. in process of moving them, need inspect every cell , if has specific value, null cell.

i have table containing meta data attributes point each cell, , list of bad values must removed original tables.

for example have following table

table1 columna | columnb | columnc |    7    |   10     |   dk     |    83   |   -7     |          |    16   |   0      |   true   | 

then meta table has

tablename | column |  bad value 1 | bad value 2 |   table1  |columnc |      dk      |    bol      |   table1  |columna |      -99     |    -1       |   table1  |columnb |      -1      |     -7      | 

in copied , transformed table1 have

table1 columna | columnb | columnc |    7    |   10     |   null   |    83   |   null   |          |    16   |   0      |   true   | 

i want understand going best way this. have considered writing function gets called each cell. passed meta information needed lookup cell in meta "bad values" table, , current value of cell, return cell value or null, depending on whether or not cell matched of corresponding bad values.

if go route, apply @ point @ read value original table, write value new table, or after copying, run separate process change value in copied table.

i need repeat process periodically (weekly).

any advice can provide appreciated.

tom

you need write cursor this, , use dynamic sql. don't think you'll find here code whole thing you, here points consideration.

• make "master" table has tables copy. i'm assuming possible have table doesn't need substitution @ point. allow modify process easily. suggested structure database, schema, table, active active being bit can toggle.

• using dynamic sql, build insert statement each table. "bad" values table populate case fields. so, case when columnc = 'dk' or columnc = 'bol' null else columnc columnc. update value "in transit" don't alter existing db target has desired changes.

• you need either have entry every column in affected tables, have separate "master" table columns per table, or query system tables list of fields copied. can't select * if need case statement.