none
Migrate data from one database to another with added fields Ask

    Question

  • Suppose you have a database A on a separate server with anywhere up to 20 tables, and database B on a different server with all the same tables but few of the tables have new columns. I want to migrate all the data from A to B and manage to keep all the new columns.

    Few of the techniques that have not worked:

    • I have tried using the import/export wizard inside SQL Server Management Tool.

    • I have also tried to generate a script from Database A and execute the script on Database B

    • I have also tried to create a BACPAC file of Database A off its server and import the database to server B and then migrate the data.
    • Lastly, I have tried to export all data in the table as SQL INSERT rows, which do not work due to primary/foreign key duplicates.

    Any sort of help would be appreciated.

    Tuesday, January 23, 2018 11:50 PM

All replies

  • Hello,

    First you can use SSDT = "SQL Server Data Tool" to generate scripts for the DB design differences=> Download SQL Server Data Tools (SSDT)
    See How to: Use Schema Compare to Compare Different Database Definitions

    The you can use a linked server to access the database on the other server to copy over the data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 24, 2018 7:46 AM
  • Hi Draklinx,

    If it is just a One-time operation rather than a real-time sync, we can use the following method:

    1. We can first migrate these tables without new columns, could you please tell us if you want to replace the data in database B using the data of database A or merging these data in database A and B?

    If it is just a replace, we can use Generate script and only select these tables without new columns in "Select the database objects to script", then change the types of data to script to data only.

    If we want to merge these two tables, please refer to step 2.

    2. We can migrate these tables with new columns with the following steps:

    Backup and Restore the database A as another name to the server which deployed database B, then use the select into from ... join ... command to create a new table and then rename the original table.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 24, 2018 7:57 AM
    Moderator
  • Hi Teige, 

    Most of the tables in Database B are empty, besides a few that are generated when I start the application. So I would like the table to replace all the data that is there, but I run into a few errors about duplicated keys in the lookup tables. 

    Is there a way to transfer all the data from all the tables in one go? Some sort of tool instead of the import/export tool?

    I will definitely try the select into command.

    Thanks


    • Edited by Draklinx Wednesday, January 24, 2018 5:28 PM
    Wednesday, January 24, 2018 5:25 PM
  • Hi Draklinx,

    If you want to replace all the data that is there, we can first delete old data from the tables, after that, Generate script and change the types of data to script to data only. Then run the script generated in the target database. 

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 29, 2018 2:33 AM
    Moderator