I have a client who wants to sync data from a legacy MSSQL 2000 DB to a MSSQL 2005 DB. The issue is that the one table I need to transfer has different column names and sometimes different values. For example, one column of named "price" in the legacy DB has a record with a value of $100. The corresponding column in the newer DB is named "priceRange" and would have a value of 1 because there's a relational table that gives a value of 1 to any int between 0 and 200.

I am not much of a DB admin so any suggestions on how to get started would be much appreciated. I've downloaded a few MSSQL syncing programs but none seem to allow you to drill into the table, they just let you sync tables across DBs which is great if your tables are identical...

Help!?

you probably need to write your own "migration" script, one that dumps the data from the legacy db and inserts into the new db with an updated schema.

Synchronization assumes that the schema will be the same, because synchronization doesn't actually create or modify the database structure, it just works with rows; sometimes it can create new tables; but that's when new tables are created on the master node.

In order to do what you are asking, you don't need synchronization, you need migration; which means you need to write an intermediary script that does the conversion for you.

If you want to avoid the intermediary step, then you need to setup both databases with the exact same schema in order for them to sync.