I am upgrading a solution from NAV 2009 to NAV 2013.  The solution was running in the Role Tailored Client only so the upgrade process was fast and easy.

I wanted to move this customized solution to another database and remove all other customizations from the database.  On the other hand I am upgrading everything in the old database except this customized solution with the standard methods.

After I had the solution upgraded and the new database ready I started to think about how I would get the data from the old database.  It is a significant amount of data so I wanted to use the native sql commands to do this.  Since I had around one hundred tables I felt it would not be practical to copy manually nor to create the copy script manually.

So, I created a batch report.  The batch creates an INSERT INTO and SELECT FROM query that is very fast.  It requires the tables to be identical in both databases.

CreateCopyScript

This batch will create a copy script that runs fast…

Few things I had to check.  When copying Blob fields from NAV 2009 to NAV 2013 I needed to change the Compressed property to No.  I also needed to check the fields to make sure that fields with AutoIncrement property set to Yes would not be included in the script.

I started with a NAV 2009 batch that is basically raw.  I made the NAV 2013 batch smarter with direct connection to the sql server.

Here are both NAV Copy for 2009 and NAV Copy 2013 (2).

12 thoughts on “Copy data between companies and/or databases

  1. chris says:

    Hi Gunnar,

    thank you for that, good job, helped me a lot!

    The version for NAV2013 threw an exception because of an not instantiated .NET variable. After commenting out the call to GetServerName in the OnOpenPage trigger it worked.

    I had to correct the resulting SQL script because some of the fieldnames contained a % sign which was not converted correctly for the 2009 database

    regards,
    Chris

  2. I updated the attachment for NAV 2013

    1. Anthony says:

      Hi good work, I get this error when I run the script in SQL,
      Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF.

      1. Hi
        In the NAV 2013 version I am checking the fields in the SQL to skip fields with “AutoIncrement” property set to true.
        What you can do is to change this property on the destination table to false while copying and the return it back to true after copying.

    2. Anthony says:

      Hi Iget this error when running the report
      Unable to convert from Microsoft.Dynamics.Nav.Runtime.NavDotNet to System.Int32.
      On debugging the error affects >> CharValueAsInt := SQLCommand.ExecuteScalar;

      1. Use the debugger to copy the SQL statement that causes this error and try it in a manual query. Are you pointing to the correct server and database ?

  3. Patrick says:

    Hi, the error “Unable to convert from…” results from “data per company” property. it is not possible to copy table with the property “data per company” = no

  4. Prajeesh Nair says:

    Hi Gunner,

    Have you seen Kine’s Blog regarding an SQL Script which enables Copy of company from one Database to another, which can be automated. I am facing a problem with it, like if the structure of Source or Destination is different the Query fails. Do you have any updates on this?. I would be very helpful if you have any. Thanks
    Regards,
    Prajeesh.

    1. Now that is the difference. This code will compare the fields in both databases and the ones that do not match will be initialized.

      1. Prajeesh Nair says:

        Thanks Gunnar, Can we achieve this in the SQL side?. I was planing an Automation SQL Job which will run every night replacing the Test Database.

  5. The output of this NAV code are SQL commands. You can use that for your nightly job. When it fails you can use this tool to rebuild your SQL commands.

    1. Prajeesh Nair says:

      Thanks, will give a try.

Leave a Reply

%d bloggers like this: