This post is entirely based on the following script by Kine
http://dynamicsuser.net/nav/b/kine/posts/copy-microsoft-dynamics-nav-company-by-sql-script-into-another-database-v2. If you read the comments you will note that a few people found bugs and Kine suggested he had some fixes, but I cannot find anything else.

So I took it on myself to fix the bugs (they were mostly around allowing different company names, which required quite a bit of restructuring of code) – and also added some optional features that were useful to my particular implementation such as a table filter and where condition. This also allows you to copy between tables which do not exactly match, so if you’re reading through and wondering what the coalesce command is doing, this is because NAV does not allow you to insert NULL values into most columns.

Hope this is helpful – let me know if you have any bugs. But I can’t guarantee I will be able to fix them 😉

-- =============================================
-- Author:        Nikolai L'Estrange
-- Create date:      19/04/2016
-- Description:   Function for copying Tables from one database and company to another 
--                based on http://dynamicsuser.net/nav/b/kine/posts/copy-microsoft-dynamics-nav-company-by-sql-script-into-another-database-v2 by Kamil Sáček 18.10.2013
--                with bugfixes around source and target companies
-- =============================================

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_NAVCopyTables')
    DROP PROCEDURE [dbo].[sp_NAVCopyTables]
GO

CREATE PROCEDURE [dbo].[sp_NAVCopyTables] 
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max),
    @tableslike varchar(max) = null,
    @whereCondition varchar(max) = null,
    @skipDelete int = null,
    @showSQL int = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    declare @targettable varchar(1000)
    declare @sourcetable varchar (max)

    declare @targetcolumns varchar(max)
    declare @sourcecolumns varchar(max)
    declare @targetcolumnname varchar (max)
    declare @sourcecolumnname varchar (max)
    
    declare @isidentity int
    declare @isidentitycolumn int
    declare @sqlcommand nvarchar (max)
    declare @tablevar table(name varchar(300))
    declare @targetcolumntablevar table(COLUMN_NAME varchar(300),DATA_TYPE nvarchar(100))
    declare @sourcecolumntablevar table(COLUMN_NAME varchar(300))
    declare @identitycolumns table(COLUMN_NAME varchar(300))
    declare @copiedtables table(SourceTableName varchar(100),TargetTableName varchar(100),NoRecordsCopied int)
    declare @copiedcount int

    if (@skipDelete is not null) and (@skipDelete < 1) set @skipdelete = null
    if (@showSQL is not null) and (@showSQL < 1) set @showSQL = null
    if (@tableslike is not null) and (@tableslike = '') set @tableslike = null
    if (@whereCondition is not null) and (@whereCondition = '') set @whereCondition = null

    if LEFT(@targetdb,1) != '['
        set @targetdb = '['+@targetdb+']'

    set @sqlcommand = '   select name collate Latin1_General_100_CI_AI from '+@targetdb+'.sys.all_objects ' 
                      +' where type=''U'' and object_id>0 and name like '''+@targetcompany+'$%'''
    if @tableslike is not null 
        set @sqlcommand += ' and name like '''+@tableslike+''''
            
    if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT 
    insert into @tablevar(name) exec sp_executesql  @sqlcommand

    RAISERROR('Found %d potential tables to copy', 0, 1,@@ROWCOUNT) WITH NOWAIT 
    RAISERROR('', 0, 1) WITH NOWAIT 

    DECLARE table_cursor CURSOR for
      select name from @tablevar
    OPEN table_cursor
    
    FETCH NEXT FROM table_cursor 
    INTO @targettable
    
    WHILE @@FETCH_STATUS = 0
    BEGIN                
        select @sourcetable= @sourcecompany+SUBSTRING(@targettable,LEN(@targetcompany)+1,LEN(@targettable)-LEN(@targetcompany)+1)

        if exists(select * from sys.all_objects where type = 'U' and object_id>0 and name collate Latin1_General_100_CI_AI = @sourcetable) begin
            RAISERROR('Copying %s to %s.%s', 0, 1,@sourcetable,@targetdb,@targettable) WITH NOWAIT 
            if @showSQL is not null RAISERROR('', 0, 1) WITH NOWAIT 

            set @sqlcommand = '   SELECT name FROM '+@targetdb+'.sys.columns as S WHERE S.object_id = OBJECT_ID('''+@targetdb+'.dbo.['+@targettable+']'') and S.IS_IDENTITY = 1'
            DELETE from @identitycolumns
            if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT 
            insert into @identitycolumns(COLUMN_NAME) exec sp_executesql  @sqlcommand

            set @sqlcommand = '   SELECT COLUMN_NAME collate Latin1_General_100_CI_AI,DATA_TYPE FROM '+@targetdb+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@targettable+''' and COLUMN_NAME != ''timestamp'''
            DELETE from @targetcolumntablevar
            if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT 
            insert into @targetcolumntablevar(COLUMN_NAME,DATA_TYPE) exec sp_executesql  @sqlcommand
    
            set @sqlcommand = '   SELECT COLUMN_NAME collate Latin1_General_100_CI_AI FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@sourcetable+''' and COLUMN_NAME != ''timestamp'''
            DELETE from @sourcecolumntablevar
            if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT 
            insert into @sourcecolumntablevar(COLUMN_NAME) exec sp_executesql  @sqlcommand
        
            DECLARE column_cursor CURSOR for 
			    select t.COLUMN_NAME,
				        coalesce(s.COLUMN_NAME,
						        case t.DATA_TYPE 
                                    when 'varbinary' then '0'
                                    when 'nvarchar' then ''''''
                                    when 'decimal' then '0'
                                    when 'varchar' then ''''''
                                    when 'datetime' then '''1753-01-01 00:00:00.000'''
                                    when 'tinyint' then '0'
                                    when 'int' then '0'
                                    when 'uniqueidentifier' then '''00000000-0000-0000-0000-000000000000'''
                                    when 'bigint' then '0'
                                    when 'image' then NULL
                                    else NULL
                                end)
                from @targetcolumntablevar as t 
                left outer join
                @sourcecolumntablevar as s on t.COLUMN_NAME = s.COLUMN_NAME

            set @isidentity = 0
            set @targetcolumns=''
            set @sourcecolumns=''
            OPEN column_cursor
            FETCH NEXT from column_cursor
            INTO @targetcolumnname,@sourcecolumnname
            WHILE @@FETCH_STATUS=0
            BEGIN
                if @sourcecolumnname is not null begin
                    select @isidentitycolumn = count(*) from @identitycolumns where COLUMN_NAME = @targetcolumnname

                    if @targetcolumns != '' set @targetcolumns += ','
                    if @sourcecolumns != '' set @sourcecolumns += ','

                    if @sourcecolumnname = @targetcolumnname begin 
                        SET @targetcolumns += '['+@targetcolumnname+']'
                        SET @sourcecolumns += '['+@sourcecolumnname+']'
                        set @isidentity += @isidentitycolumn                    
                    end else if @isidentitycolumn = 0 begin
                        SET @targetcolumns += '['+@targetcolumnname+']'
                        SET @sourcecolumns += @sourcecolumnname
                    end
                end
                FETCH NEXT from column_cursor
                INTO @targetcolumnname,@sourcecolumnname
            END
            CLOSE column_cursor;
            DEALLOCATE column_cursor;

            --RAISERROR (@sourcecolumns, 0, 1) WITH NOWAIT
            --RAISERROR (@targetcolumns, 0, 1) WITH NOWAIT

            if @targetcolumns != '' begin
                --set nocount off
                if @skipDelete is null begin
                    set @sqlcommand = '   DELETE from '+@targetdb+'.dbo.['+@targettable+']'
                    if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT
                    exec sp_executesql @sqlcommand
                end
                
                set nocount off

                set @sqlcommand = ''            
                IF (@isidentity>0)
                  set @sqlcommand += '   SET IDENTITY_INSERT '+@targetdb+'.dbo.['+@targettable+'] ON;'

                set @sqlcommand += '   INSERT into '+@targetdb+'.dbo.['+@targettable+'] ('+ @targetcolumns + ')'
                + ' SELECT '+@sourcecolumns
                + ' from dbo.['+@sourcetable+']' 
                if @whereCondition is not null
                    set @sqlcommand += ' WHERE '+@whereCondition

                IF (@isidentity>0)
                  set @sqlcommand += '; SET IDENTITY_INSERT '+@targetdb+'.dbo.['+@targettable+'] OFF'

                if @showSQL is not null RAISERROR('%s', 0, 1,@sqlcommand) WITH NOWAIT
                exec sp_executesql @sqlcommand                
                set @copiedcount = @@ROWCOUNT
                set nocount on                

                insert into @copiedtables(SourceTableName,TargetTableName,NoRecordsCopied) select @sourcetable,@targettable,@copiedcount
            end
            RAISERROR('', 0, 1) WITH NOWAIT
        end
        FETCH NEXT FROM table_cursor 
        INTO @targettable
    END 
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
    select * from @copiedtables;
END
 

EDIT: I’ve made a couple more fixes related to the comments below and also want to clear up some confusion (mostly mine, I can never remember how to run it) about whether you need to put square brackets around Database or Company names. In my stored procedure you don’t – it will add what it needs (in Kine’s you did, sometimes…)

So the simplest way to run it is below (the other parameters are optional, don’t use them unless you can work out what they do).

use [Demo Database NAV W1]
sp_NAVCopyTables 'CRONUS International Ltd_','My New Demo Database','Test'