Print ''
Print '----------------------------------------------------'
Print ' Import Procedures for JobMgr:'
Print ' FirstRevision: V2.0.0   22.03.2006'
Print ' CurrRevision : V2.2.1   26.02.2010'
Print ' Autor        : J.Feuerstein'
Print ' '
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 nvarchar(max) ,
                                       @FromDb nvarchar(max) ,
                                       @ToDb   nvarchar(max) ,
                                       @DelDateAtDestTable  nvarchar(max) -- 'DelAllDataAtDestTable' = loescht alle Daten vor dem Transver der Daten
/* Transferiert alle Daten einer Tabelle von der QuellDatenbank in eine Zieldatenbank ein Vorausetzung beide Tabellen sind vorhanden !*/
As
BEGIN

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @fromDbTable nvarchar(max)
  DECLARE @fromDbTableExist bit
  DECLARE @fromDbTableNDataSets Integer
  DECLARE @toDbTable nvarchar(max)
  DECLARE @toDbTableExist bit

  set @fromDbTable = @FromDb + N'.dbo.'+ @TableName
  set @toDbTable   = @ToDb +   N'.dbo.'+ @TableName
  Print ''
  Print '----------------------------------------------------'
  Print ' Transfer all datasets'
  Print ' Table  :' + @TableName
  Print ' fromDb :' + @FromDb
  Print ' toDb   :' + @ToDb


 if @DelDateAtDestTable = 'YES'
     BEGIN
       Print '---'
       Print ' Step: delete all datasets in table:' + @toDbTable
       set @ExecuteSQLstr = N'DELETE FROM ' + @toDbTable
       execute sp_executesql @ExecuteSQLstr
     END

 Print ' Step:insert all datasets in table:' + @toDbTable
 set @ExecuteSQLstr = N'INSERT INTO ' + @toDbTable +
                      N' SELECT ' + @fromDbTable + '.*' +
                      N' FROM ' + @fromDbTable
 execute sp_executesql @ExecuteSQLstr

 set @ExecuteSQLstr = N'SELECT COUNT(*) FROM ' + @fromDbTable
 execute sp_executesql @fromDbTableNDataSets = @ExecuteSQLstr
 Print 'Res: DataSets from:' + CONVERT(varchar(100), @fromDbTableNDataSets)

END
GO
Print 'Doku:'
Print '--------------------------------------------------------------------------------'
Print ' Diese SQL Script uebertraegt Einstellunen von einer JobMgrDb in eine andere'
Print ' es kann nur angewendet werden wenn die Datenbanken in der selben instance'
Print ' befinden.'
Print ' Aktuell (26.02.2010) geht das Script nur wenn die Reihenfolgen der Datenspalten in den'
Print ' Tabellen gleich sind'
Print '--------------------------------------------------------------------------------'

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

-- ## @FromDb und @ToDb Namen fest vergeben
--    Tipp: siehe JobServer->Extras->Settings->Regsiterkarte JobServer->MSSQL_DB_NAME
set @FromDb = 'JobManagerV2_CustomerNameShort_WorkInst_TEST'
set @ToDb = 'JobManagerV2_CustomerNameShort_WorkInst_PROD'

-- Datenbank Name Dynamisch zusammen setzen
declare @CustomerNameShort     nvarchar(max)
declare @FromDb_JobServer_WorkInstanc_NameCurr  nvarchar(max)
declare @ToDb_JobServer_WorkInstanc_NameCurr    nvarchar(max)

-- ## @CustomerNameShort vergeben z.B. JF IHK BMG ..
set @CustomerNameShort = 'CustomerNameShort'
-- ## @FromDb_JobServer_WorkInstanc_NameCurr und @ToDb_JobServer_WorkInstanc_NameCurr vergeben
set @FromDb_JobServer_WorkInstanc_NameCurr = '_WorkInst_TEST'
set @ToDb_JobServer_WorkInstanc_NameCurr = '_WorkInst_PROD'

set @FromDb = 'JobManagerV2_' + @CustomerNameShort + @FromDb_JobServer_WorkInstanc_NameCurr
set @ToDb =   'JobManagerV2_' + @CustomerNameShort + @ToDb_JobServer_WorkInstanc_NameCurr

-- ##  @DelAllDataAtDestTable festlegen
--     'NO'  heist alle bestehenden Daten bleiben im Ziel erhalten
--     'YES' heist alle bestehenden Daten werden zuerst gelöscht und dann von der Quelle ins Ziel uebertragen
declare @DelAllDataAtDestTable  nvarchar(max)
set @DelAllDataAtDestTable='NO'

set @TableName = 'ProcessGroups_tbl'
EXECUTE TransDataOfTableFromDbToDb @TableName , @FromDb , @ToDb , @DelAllDataAtDestTable

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

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

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

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

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


GO

Print ''
Print '----------------------------------------------------'
Print ' Procedures imported to: [#DB_N#]'
Print '----------------------------------------------------'
GO
