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!
