Data migration in SQL server2005 two-table Association update data Operations

Recently in the work of data migration, the old system of data migration to the new system, there is a lot of trouble, because it is a redesigned system and the old system has too many inconsistencies, such as table structure, field processing, such as the storage of attachments, as well as historical legacy data ...

Later will slowly the relevant technical processing details to show out, of course, not very difficult, but trivial things too much.

Take a look at the associated updates for your data today.

Business Scenario:

The same business, there is a main table and a sub-table, the field is similar, only the old system of the primary key generation method GUID, and the new primary key generation mode from the growth type, here is not reasonable, to see our problem.

All we have to do is import the old data into the new table and keep their associations, if not the wrong one.

Suppose the business object:

The fields you want to use, the other slightly

Two cousin names: Old_table (GUID), Old_table_sum (OLD_TABLE_GUID);

Two new table names: Table (ID), Table_sub (table_id)

So we're going to deal with them now:

The first step is to add the field GUID to the new table and prepare the associated data.

ALTER TABLE dbo. TABLE Add GUID char (38)

ALTER TABLE dbo. Table_sub Add GUID char (38)

The GUID in the table_sub is the old_table_sub old_table_guid);

Everyone should have understood it.

The second step is to copy the table data.

This is handled according to the differences in the table structure, which is a little bit.

is to copy the old_table data to the TABLE.

Copy the old_table_sub data to Table_sub

At this point, all the data is available,

The third step is to modify the data to make the two table relationships.

UPDATE Tempa
SET
Tempa. [table_id] = Tempb.id
From Table_sub tempa,table TEMPB
WHERE Tempa.guid = Tempb.guid

So then the business association of the two tables has been established.

The fourth step is to remove the field you just added.

The rest of the technical details are slowly being sent back.

Email: [Email protected]

qq:307266032

Reprint Please note the source!

Data migration in SQL server2005 two-table Association update data Operations