Print ''
Print '--------------------------------------------------------------------------------'
Print ' Transfers JobManager settings beween 2 JobManager Databases:'
Print ' Autor        : J.Feuerstein'
Print ' FirstRevision: V2.0.0   22.03.2006'
Print ' CurrRevision : V2.2.1   30.01.2015'
Print '--------------------------------------------------------------------------------'
GO
USE [#DB_N#]

Print '-'
Print '-- [TransDataOfTableFromDbToDb] ------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "TransDataOfTableFromDbToDb" and type = "P")
Drop Procedure TransDataOfTableFromDbToDb
GO

Create Procedure TransDataOfTableFromDbToDb
                                       @TableName_p nvarchar(max) ,
                                       @fromDB_p nvarchar(max) ,
                                       @toDB_p   nvarchar(max)
/* transfers all data of tabe alle Daten einer Tabelle von der QuellDatenbank in eine Zieldatenbank !*/
As
BEGIN

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @fromDbTable nvarchar(max)
  DECLARE @toDbTableNDataSets Integer
  DECLARE @toDbTable nvarchar(max)

  SET NOCOUNT ON


   set @fromDbTable = @fromDB_p + '.dbo.'+ @TableName_p
   set @toDbTable   = @toDB_p +   '.dbo.'+ @TableName_p
   Print ''
   Print '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -'
   Print ' Transfer all datasets'
   Print ' fromDb -> toDb......: ' + @fromDB_p  + ' -> ' + @toDB_p
   Print ' for Table...........: ' + @TableName_p
   Print ' from Table..........: ' + @fromDbTable
   Print ' to Table............: ' + @toDbTable

  set @ExecuteSQLstr = N'Drop table ' + @toDbTable
  if (object_id(@toDbTable) is not null)
  begin
   -- Print ' Remove table........: ' + @toDbTable
   exec ('drop table ' + @toDbTable)
  end

  if (object_id(@toDbTable) is null)
  begin
   Print ' Create table........: via select * into ... is in process...'
   exec('select * into ' + @toDbTable + ' from ' + @fromDbTable)
   Print ' Result..............: OK: ' + CONVERT(varchar(100),  @@ROWCOUNT) + ' DataSets tranfered'
  end
  else
  BEGIN
   Print ' Delete table........: ERR: cannot delete table: ' + @toDbTable
   Print ' Result..............: ERR: NO DataSets tranferd'
  END

END
GO


Print 'Doku:'
Print '--------------------------------------------------------------------------------'
Print ' This SQL Script transfers all Settings from an existing JobManager Database to an'
Print ' new JobManger Database'
Print ' Please keep in Mind:'
Print ' All existing settings target JobManager Database will be overritten'
Print '--------------------------------------------------------------------------------'

declare @FromDb                nvarchar(max)
declare @ToDb                  nvarchar(max)
declare @TableName             nvarchar(max)

 -- #toDo: define @FromDb and @ToDb Name
 -- Tipp: see JobServer->Extras->Settings->Regsiterkarte JobServer->MSSQL_DB_NAME
 -- #toDo: set @FromDb and @ToDb
 set @FromDb = 'JobManagerV3_CustomerNameShort_WorkInst_TestSystem'
 set @ToDb   = 'JobManagerV3_CustomerNameShort_WorkInst_ProdSystem'

 -- List of table to transfer:
 set @TableName = 'ProcessGroups_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

 set @TableName = N'Sites_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

 set @TableName = 'JobScript_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

 set @TableName = 'JobControlRules_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

 set @TableName = 'JobComputers_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

 set @TableName = 'TaskScheduler_tbl'
 EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb 

Print ''
Print '--------------------------------------------------------------------------------'
Print ' Process finished'
Print '--------------------------------------------------------------------------------'
Print ' #ToDo: please reorg your database via:'
Print '    Extra -> Setup -> Setup database -> Database Maintain -> 21 - Upgrade via (OSQL)'
Print '--------------------------------------------------------------------------------'
GO
