AXForum  
Вернуться   AXForum > Microsoft Dynamics NAV > NAV: Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 15.05.2016, 17:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,631 / 848 (80) +++++++
Регистрация: 28.10.2006
Kine’s info: Copy Microsoft Dynamics NAV company by SQL script into another database v2
Источник: http://blogs.msmvps.com/kine/2014/01...r-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)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 @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!



Источник: http://blogs.msmvps.com/kine/2014/01...r-database-v2/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
crminthefield: Podcast and Overview: Microsoft Dynamics CRM 2011 Update Rollup 8 Blog bot Dynamics CRM: Blogs 1 30.04.2016 10:26
crminthefield: Podcast and Overview: Microsoft Dynamics CRM 2011 Update Rollup 17 Blog bot Dynamics CRM: Blogs 0 10.05.2014 06:30
atinkerersnotebook: Walkthrough & Tutorial Summary Blog bot DAX Blogs 1 09.09.2013 09:11
axinthefield: Dynamics AX Event IDs Blog bot DAX Blogs 0 01.03.2011 22:11
Nav developer: Overview of NAV blogs in 2008 Blog bot Dynamics CRM: Blogs 0 07.12.2008 02:18
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 00:21.