Kine's Info

What I found and what I know about Microsoft Dynamics NAV

Copy Microsoft Dynamics NAV company by SQL script into another database

After Microsoft Dynamics NAV 2013 R2 release, there are complains about impossibility to copy company from one database to another (you can copy company inside database through standard NAV client interface). Thus I spent some time creating stored procedure which will do it for you… or, this stored procedure will generate script for you, which will do it for you… of course, you can modify the script as you wish to fix possible bugs or extend the functionality and I will be happy when you will share your versions with us.


There is script to create the stored procedure:

USE master
-- =============================================
-- Author:        Kamil Sáček
-- Create date: 18.10.2013
-- Description:    Function for copying comany from one database to another
-- =============================================
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max)
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- Insert statements for procedure here
    declare @tablename varchar(1000)
    declare @columns varchar(max)
    declare @columnname varchar (max)
    declare @targettable varchar (max)
    declare @isidentity int

    DECLARE table_cursor CURSOR for
      select name from sys.all_objects where type='U' and object_id>0 and name like @sourcecompany+'$%'
    OPEN table_cursor

    FETCH NEXT FROM table_cursor 
    INTO @tablename
        DECLARE column_cursor CURSOR for
        select @columns=''
        OPEN column_cursor
        FETCH NEXT from column_cursor
        INTO @columnname
            SELECT @columns=@columns+',['+@columnname+']'
            FETCH NEXT from column_cursor
            INTO @columnname
        CLOSE column_cursor;
        DEALLOCATE column_cursor;
        select @columns = SUBSTRING(@columns,2,LEN(@columns)-1)
        select @targettable= @targetdb+'.dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'
        print 'RAISERROR ('''+REPLACE(@targettable,'%','%%')+''' , 0, 1) WITH NOWAIT '
        IF (@isidentity>0)
          print 'SET IDENTITY_INSERT '+@targettable+' ON'

        print 'delete from '+@targettable
        print 'insert into '+@targettable+ ' ('+ @columns + ')'
        print '    select '+@columns
        print '       from ['+@tablename+']' 

        IF (@isidentity>0)
          print 'SET IDENTITY_INSERT '+@targettable+' OFF'

        FETCH NEXT FROM table_cursor 
        INTO @tablename
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
------------ Update 6.11.2013--------
After that you need to mark the SP as system by running this, else the SP will return no data (will work with master DB instead actual DB):

EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany
------------End of update -----------
After you create the stored procedure on the server, you can use it by running something like this:
use myNavDatabase
exec sp_NAVCopyCompany 'CRONUS International Ltd_','NAV2009R2_W1','Test'

In output window you will have script with needed commands which will copy the data. Copy it, run it. It will do what you need. If you focus the output window after starting the script (by pressing F6 for example), you will see the tables which are copied.


This script copies all “per company” tables and it is able to copy the data into database, in which you already prepared the company (all existing data in the target company are deleted!). It means, the structure of tables must be same between source and target company!

You can use this script to “update” existing copy of the company to keep e.g. testing company actual Veselý obličej

Usage of this script are on your own danger!


I wish you many copied companies without any problem…

  • Thanks Kine, this is very helpful! :)

  • Hi Kine

    I did a solution in NAV that created a copy script.  In that script I had to skip fields with AutoIncrement set to True.

    Is that not a problem in you procedure ?

  • @Gunnar: It is solved by setting the "SET IDENTITY_INSERT" for tables having identity column.

  • Hi Kine,

    I tried to use this stored procedure but it's not working for me.

    The sp is succesfull created under the master database.

    - If I run the sp directly, the result is: 0.

    - If I run the sp by code, the result is: Command(s) completed successfully.


    use Dynamics_NAV

    exec sp_NAVCopyCompany 'SourceCompany','NAVDB','NewCompany'


    Both action are super fast completed, and the new company is not created.

    After this, I've created the NewCompany manually and try these steps again: same result.

    What do I wrong?

  • I have notice that few days ago. I think it depends on SQL version. It seems that in some configuration the "select name from sys.all_objects " is running under MASTER db and not in your context (from DB you are running the sp from).

    It seems that you need to mark the SP as systém object by:

    "EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany"

    And than the SP should generate script, which you need to copy and run (it will copy the data to already existing company, deleting existing data in it first.)

  • Hi,

    I've just tried this script. But couldn't get it working. I'm trying on a SQL server 11.0.2100.60. The SP is generated, then I ran the

    EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany

    And then

    use myNavDatabase

    exec sp_NAVCopyCompany 'SourceCompany','DestinationDB','DestinationCompany'

    I have created the Company before running the script.

  • Hi Kine,

    I've tried exec the script. Then exec the "EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany".

    And finally:

    use SourceDB

    exec sp_NAVCopyCompany 'SourceCompany','DestinationDB','DestinationCompany'

    I see the script running and finishing with no errors. But no data is copied.

    SQL server is ver. 11.xx

  • You need to take the output of the script and run it as script. This stored procedure is only "generator" for the copy script.

  • It's worked for me. Thank you.

  • As iam trying on it. it is not copying effected data from source tables to destination tables.

  • @Navaneeth> And have you run the generated script? Or you just run the stored procedure?

  • Running on SQL 2008 R2. No script gets generated.  sp runs without errors.  I have 15 companies that need to be set up from a template.  Can you please assist?

  • have you runthe 'EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany'  command to mark the SP as system?

  • Found a minor issue when using this excellent sp.

    When running the sp, if you have dashes (and probably other strange characters) in your database name it fails since there is no [] around the target database name.

    Change line 53 from:

           select @targettable= @targetdb+'.dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'


           select @targettable= '['+@targetdb+'].dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'

    Thanks for sharing your work!

  • Or you can just enter the parameter (the name of the database) including the []... ;-)