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'
7 thoughts on “Copy NAV Data between companies and databases using SQL”
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.
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.
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.
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.
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
*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…
Well, that seems to confirm it (hope you’ve seen my EDIT) I can’t put in < > in code as it gets stripped out by wordpress
Hi All, thank you for the script and fixes,
my question is: IN WICH SCENARIO MIGHT SUCH A SCRIPT BE USEFUL?
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.