Copy NAV Data between companies and databases using SQL

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' 
Advertisements

7 thoughts on “Copy NAV Data between companies and databases using SQL

  1. I’ve tried to run the Create SP above and I’ve getting the following:
    Msg 102, Level 15, State 1, Procedure sp_NAVCopyTables, Line 50
    Incorrect syntax near ‘[‘.
    Msg 102, Level 15, State 1, Procedure sp_NAVCopyTables, Line 125
    Incorrect syntax near ”.
    Msg 102, Level 15, State 1, Procedure sp_NAVCopyTables, Line 126
    Incorrect syntax near ”.
    Msg 102, Level 15, State 1, Procedure sp_NAVCopyTables, Line 146
    Incorrect syntax near ”.

    I’m using the GB release of NAV for the most part and one instance of W1. I’ve tried running on 6.0, 7.0, 8.0 and 9.0 and I get exactly the same each time.

    Running on:
    Microsoft SQL Server 2014 – 12.0.4213.0 (X64)
    Jun 9 2015 12:06:16
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )

    Unfortunately I’m not able to debug SQL code. I’m really hoping to find a SQL way of copying companies across databases or within Databases. We have some large companies and doing the copy through the service with Table Locks is just too long and disruptive to the customer. I was hoping that the Powershell Scripts might help, but they just run through the Service too. Hence I’ve been trying Kines V1 and V2 and now your version and there is always an issue somewhere. I presume it’s to do with the different versions of SQL that they were written on.

    Regards

    Ben

    Like

    1. Can you confirm the code you are using to call the Stored Procedure?

      The errors look like the procedure doesn’t like one of your Table names – which could be to do with the Company Names or a difference in how SQL Server 2014 names the tables (this would be news to me) – or it could be a bug in the code above.

      Rather than debugging you might need to try the cut-and-slice method of analysing – i.e. start filtering stuff out to see if we can work out which component causes the bug.

      Nikolai

      Like

      1. Thanks for the response.

        The issue isn’t with calling the Stored procedure, it’s with running the code above to create the stored procedure. It doesn’t create the SP in first instance.

        Once

        Ben

        Like

      2. Oh! Well that is a bug with the SQL code then.

        Looks like there are a few lines where the not equals signs have been stripped out of my code (??? I blame wordpress) – they should be underlined red in SQL Query editor.

        One at line 50 (https://sqlandme.com/2014/01/20/sql-server-displaying-line-numbers-in-query-editor-ssms/)
        if LEFT(@targetdb,1) != ‘[‘

        Then at lines 125,126 & 146
        if @targetcolumns != ”

        Hope this solves your issue

        Nikolai

        *EDIT* Sorry they should all be not equals (!=). Fixed above and I’ll go back and fix the code in the blog post. Possibly in the original code I used so it thought I was putting an empty html tag…

        Like

    1. While you can Export and Import Companies between databases with standard NAV – it is very very strict about having the exact same object set. This is not.

      I mostly use it to refresh a DEV or UAT database from a LIVE database – especially when I know the objects won’t be exactly the same.

      I also use it to copy “Demo” or “Base Setup” companies around my environments.

      And lastly I use it with the Tables and Where filters when I want to copy only a subset of data (I could use RapidStart Packages but I find this easier) between companies in the same database or between databases. Because it is a stored procedure I have actually used it as part of an Intercompany/Interdatabase Synchronisation process that is regularly run from NAV.

      And lastly, it is much faster than the Export/Import company or using RapidStart.

      It’s not magic though – you do need to test it and pay attention to the output if you are working on LIVE systems.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s