Copy Microsoft Dynamics NAV company by SQL script into another database v2

Because the first script I have released was “two step job” and it is too complicated to use it in some automated way, I have prepared this new script, which is only modification of the original. This new script directly copy the data in one go, you do not need to save the script and execute it in second step. This SP do not need to be marked as system.

 

-- =============================================
-- Author:        Kamil Sáček
-- Create date: 18.10.2013
-- Description:    Function for copying comany from one database to another
-- =============================================
CREATE PROCEDURE [dbo].[sp_NAVCopyCompany_v2] 
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max)
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 @tablename varchar(1000)
    declare @columns varchar(max)
    declare @columnname varchar (max)
    declare @targettable varchar (max)
    declare @isidentity int
    declare @sqlcommand nvarchar (max) = 'select name from '+@targetdb+'.sys.all_objects where type=''U'' and object_id>0 and name like '''+@sourcecompany+'$%'''
    declare @sqlcommandIdentity nvarchar (max)
    declare @tablevar table(name varchar(300))
    declare @columntablevar table(COLUMN_NAME varchar(300))
    declare @identitytablevar table(C int)
    insert into @tablevar(name) exec sp_executesql  @sqlcommand
    DECLARE table_cursor CURSOR for
      select name from @tablevar
    OPEN table_cursor

    FETCH NEXT FROM table_cursor 
    INTO @tablename
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --RAISERROR (@tablename, 0, 1) WITH NOWAIT 
           set @sqlcommand = 'SELECT COLUMN_NAME FROM '+@targetdb+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tablename+''' and COLUMN_NAME <> ''timestamp'''
        DELETE from @columntablevar
        insert into @columntablevar(COLUMN_NAME) exec sp_executesql  @sqlcommand
        DECLARE column_cursor CURSOR for select COLUMN_NAME from @columntablevar
           
        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)
        --RAISERROR (@columns, 0, 1) WITH NOWAIT 
        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
        --
        set @sqlcommandIdentity = 'SELECT COUNT(*) as C FROM '+@targetdb+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+@tablename+''' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1'
        DELETE from @identitytablevar
        insert into @identitytablevar(C) exec sp_executesql  @sqlcommandIdentity
        select @isidentity=SUM(C) FROM @identitytablevar

        RAISERROR (@targettable, 0, 1) WITH NOWAIT 
        set @sqlcommand = ''
        IF (@isidentity>0)
          set @sqlcommand = @sqlcommand + 'SET IDENTITY_INSERT '+@targettable+' ON;'

        set @sqlcommand = @sqlcommand + 'delete from '+@targettable+';'
        set @sqlcommand = @sqlcommand + 'insert into '+@targettable+ ' ('+ @columns + ')'
        + '    select '+@columns
        + '       from ['+@tablename+']' 

        IF (@isidentity>0)
          set @sqlcommand = @sqlcommand + ';SET IDENTITY_INSERT '+@targettable+' OFF'
        --RAISERROR (@sqlcommand, 0, 1) WITH NOWAIT 
        exec sp_executesql @sqlcommand
        FETCH NEXT FROM table_cursor 
        INTO @tablename
    END 
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END



.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 



This SP could be run like this:



use [Demo Database NAV (7-1) W1]
sp_NAVCopyCompany 'CRONUS International Ltd_','[Demo Database NAV (7-1) W1]','Test'


.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 



Have a nice copies of the companies!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>