none
How can i achieve this RRS feed

  • Question

  • I have a task to get a utility APP  which would basically help to move the application data from one system to another 
    Ex: Prod System to Dev System or Dev to Q.A system
    My database have multiple tables and have relationship with each other.
    Now I have wondering what would be the best way to move data so that the the data does not get messed up.
    Remember I have add to the existing data.

    Example:

    Production Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    1
    2   Tom     24    4
    3   Hary    25    1
    4   Arnold  26    3

    AddressDetails

    Id PlaceName
    1  newyork
    2  California
    3  canada
    4  Dubai

    Dev Environment                              
    My Database contains two tables
    Student and AddressDetails

    Student;
    Id  Name    Age AddressId
    1   Jerry   23    2
    3   Hary    25    1


    AddressDetails

    Id PlaceName
    1  newyork
    2  California

     Now What I want to with my utility App is it will show me the list of a students as a list and I can select the student information that I want to move from prod to dev
    In the above scenario:

    My app should only show Tom and Arnold and I can select which i need to move . This should happen in a single transaction . This is just an example and I have 50 tables like this which have relationships within.
    Please provide your suggestion and the best way to achive this.
    Friday, January 12, 2018 10:49 AM

All replies

  • That is a fairly complex operation to start off with, the following is high level thoughts and advice, zero code.

    You not only need to copy from database to database but also need to consider the following.

    Keeping the same primary keys which means you need to work with SET IDENITY_INSERT. Couple this with "what about existing data in the target tables?", you need to wipe the existing data. Then of course is writing your SQL SELECT statements with the appropriate WHERE condition to insert data into the other database. One might consider MERGE statement but that may have issues.

    As a rule of thumb it's never wise to move production data to lower environments but instead to mock up data but if you must there are no shortcuts to moving data, especially 50 tables that have relationships.

    We only push references tables from dev to QAC to production, not the reverse and when doing so primary keys must be retained and that is all done without C# code but instead stored procedures. 

    Bottom line is you should focus on SQL, get that working say in SQL-Server Management Studio and then have a user interface to permit a user to select data to move to the secondary database and then call the stored procedure passing in primary keys to do the SQL operation.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 12:54 PM
    Moderator