Mapping fields from two tables

Hi all,

I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.

What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example

CURRENT SITE

Table 1
Field1: Name
Field2: Address

NEW SITE

Table 1
Field1: Name
Field2: myAddress
Field3: City

I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:

(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress

The city field would be left unaffected as that data would only be entered once the new site is up and running.

I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?

Cheers,
JeffYou can use DTS to copy the data from one database to the next. I've done hundreds of thousands of rows in a short period of time.|||Yeah I did know of the DTS and have used it in the past but there is a problem with doing that. Here's why:

As the first version of the site was programmed years ago I made alot of mistakes in the way I did things and does not match what I want to do now. One thing in partilcuar is that I want to assign categories to each news item in my database. This requires manually tagging thousands of items, but is worth the effort. The problem is that becuase of the way I am re-structuing the data and tables I want to be able to update the live data as it comes in, but also be submitting those updated categories tags to the new database. The old tables do not have fields and are not structured to accomodate the new data I want to enter. This is why I wanted some short and easy method from within SQL Server to map particulr fields from the old tables to the new tables. I woudl then start adding the new information to the new tables.

I hope that makes sense. Perhaps such a thing does not exist.|||In the DTS designer, there is an option to map particular fields from an old table structure into a new one, even across tables. As long as you aren't breaking fields up, it's not too hard to do. I forget which options to mess around with, but definitely check it out. No sense in doing work you don't need to do! :)|||I spent quite a while playing with DTS designer and was able to map fields like I wanted BUT I was not able to figure out how to perform updates to information. At least not easily. I can append data and I can overwrite exiting data, but not UPDATE it. I started playing with Replication and that seemed to do what I wanted in terms of creating real-time mirroring of the old tables into a new database, but it does not look like there is a way to map to different field names. Is there a way for me to either combine the DTS and replication or a way to map fields when I push a subscription?

Cheers,
Jeff|||I couldn't find anything on combining DTS with replication. From what I saw and from my own knowledge of SQL Server, it sounds like what you want to do would have to be custom-built either in VBScript in the DTS Designer or through stored procs, which is what we were trying to avoid in the first place! Oh well, sorry to lead you down the wrong path. Good luck!