Kine: Copy Microsoft Dynamics NAV company by SQL script into another database

[Blog bot]

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 masterSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Kamil S&aacute;ček-- Create date: 18.10.2013-- Description: Function for copying comany from one database to another-- =============================================CREATE PROCEDURE sp_NAVCopyCompany @sourcecompany varchar(max), @targetdb varchar(max), @targetcompany varchar(max)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- 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 WHILE @@FETCH_STATUS = 0 BEGIN DECLARE column_cursor CURSOR for SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename and COLUMN_NAME <> 'timestamp' select @columns='' OPEN column_cursor FETCH NEXT from column_cursor INTO @columnname WHILE @@FETCH_STATUS=0 BEGIN SELECT @columns=@columns+',['+@columnname+']' FETCH NEXT from column_cursor INTO @columnname END 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)+']' select @isidentity=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tablename AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 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 END CLOSE table_cursor; DEALLOCATE table_cursor;ENDGO------------ 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 myNavDatabaseexec 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 &ldquo;per company&rdquo; 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 &ldquo;update&rdquo; existing copy of the company to keep e.g. testing company actual [img]/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/kine.metablogapi/2021.wlEmoticon_2D00_smile_5F00_7C91CAF7.png[/img]


Usage of this script are on your own danger!





I wish you many copied companies without any problem&hellip;



Источник: http://blogs.msmvps.com/kine/2013/10...ther-database/