Copy data between companies and/or databases

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.


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).

13 Replies to “Copy data between companies and/or databases”

  1. 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


    1. 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. 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. Hello,
          I think the cause is when the property “datapercompany” is set to No on tables.

          How could I correct this bug ?

  2. 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

  3. 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

      1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.