Merging two Databases with different schema

  • What is the easiest way to Merge two databases. The databases are almost identicle, however two of the tables have an extra field in them. Do I have to use insert statements for each table and if so how do I get past the: "column name or number of supplied values does not match table definition" error

    Thanks

    Thanks For your continued Help.


    Thanks For your continued Help.

  • There's no magic, you can use either plain TSQL or DTS, for either you'll have to customize your inserts to match the structure of the table. Depending on whether those extra cols need to be populated, it may make sense to add them to the source table and populate them, making the final insert easier and letting you populate them with multiple updates as may be required to fulfill whatever rules are in place.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Sounds like what I thought. how to I add and drop single fields from a table without disturbing any other data in that table. I will have to do this because my applications write out to the database and I can not have extra fields as it will notice the mismatch and roll off any other data during the archive process.

    Thanks For your continued Help.


    Thanks For your continued Help.

  • Alter table will add/drop single fields. You can also use a temp table (create new table as tmp1, insert into tmp1 by selecting from original table, drop original then rename the tmp1 to original table name). The alter is easier.

    Terry


    Terry

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply