Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* automatic SOURCE to STAR Update */
- DECLARE @count int;
- DECLARE @sql nvarchar(max);
- DECLARE @SourceDB as varchar(150);
- DECLARE @SourceCatalog as varchar(150);
- DECLARE @SourceSchema as varchar(150);
- DECLARE @SourceTable as varchar(150);
- DECLARE @SourceQuery as varchar(150);
- DECLARE @TargetDB as varchar(150);
- DECLARE @TargetCatalog as varchar(150);
- DECLARE @TargetSchema as varchar(150);
- DECLARE @TargetTable as varchar(150);
- DECLARE @TableFilter as varchar(500);
- DECLARE @name sysname ;
- DECLARE @PK_ColumnName as varchar(150);
- DECLARE @PK_CONSTRAINT_NAME as varchar(150);
- DECLARE @PK_ID varchar(max)
- DECLARE @USERNAME as varchar(max)
- DECLARE @PASSWORD as varchar(max)
- /*************************************************************************/
- /** Logtabelle Copy and Delete **/
- /*************************************************************************/
- EXEC CFG_LOG.dbo.pr_DelUpLog
- /*************************************************************************/
- /********************** Drop and Insert INTO **********************/
- /*************************************************************************/
- INSERT INTO CFG_LOG.dbo.LogTable
- ([DateTime]
- ,[Funktion]
- ,[Text]
- ,[Status])
- SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Der Import wurde gestartet' , 'INFO' as Status ;
- DECLARE cur_cfg_sta_import CURSOR READ_ONLY
- FOR
- SELECT Query
- ,SourceDB
- ,SourceCatalog
- ,SourceSchema
- ,SourceTable
- ,TargetDB
- ,TargetCatalog
- ,TargetSchema
- ,TargetTable
- ,ISNULL(Tablefilter,'')
- ,LnkUser
- ,LnkPw
- FROM cfg_log.dbo.CFG_STA_IMPORT
- WHERE upper([UPDATE]) = 'J'
- OPEN cur_cfg_sta_import
- FETCH NEXT
- FROM cur_cfg_sta_import
- INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable, @Tablefilter, @USERNAME, @PASSWORD
- /*
- ******** Tablefilter ********
- Delticket : where datediff(mm, proddate, getdate()) between 0 and 2
- Deltickdet : where id in (select id from simma.delticket where datediff(mm, proddate, getdate()) between 0 and 2)
- *****************************
- */
- WHILE (@@FETCH_STATUS <> -1)
- BEGIN
- --BEGIN TRY
- -- BEGIN TRANSACTION
- set @name = N'' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
- IF EXISTS (SELECT * FROM STA.sys.objects WHERE object_id = OBJECT_ID( @name ) AND type in (N'U') )
- BEGIN
- IF @TableFilter =''
- BEGIN
- set @sql = 'DROP TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
- EXEC(@sql)
- PRINT ''
- PRINT 'Löschen von Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
- END
- ELSE
- BEGIN
- set @sql = 'DELETE FROM ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ' + @TableFilter
- EXEC(@sql)
- PRINT ''
- PRINT @sql
- END
- END
- BEGIN
- --IF @TableFilter = ''
- -- BEGIN
- -- set @sql = 'SELECT * INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable
- -- END
- -- ELSE
- -- BEGIN
- -- Entfernen von [] des Linked-Server-Namens
- declare @linkedserverName nvarchar(150) = REPLACE(REPLACE(@SourceDB, '[',''), ']','')
- -- Erstellen von Linked Server, wenn nicht vorhanden
- if not exists(select server_id from sys.servers where name = @linkedserverName)
- begin
- exec master.dbo.sp_addlinkedserver @server=@linkedserverName, @srvproduct=N'SQL Server'
- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@linkedserverName,@useself=N'False',@locallogin=NULL,@rmtuser=@USERNAME,@rmtpassword=@PASSWORD
- end
- -- check if non sql server based rdbms engine
- declare @query_text nvarchar(max)
- SET @query_text = 'select distinct columns.table_name from openquery(' + @SourceDB + ', '' select t.name as table_name from sys.objects o inner join (select object_id, name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id'') columns where columns.table_name = ''' + @TargetTable + ''''
- declare @ret_val int
- set @ret_val = 0
- BEGIN TRY
- begin transaction
- exec sp_executesql @query_text
- set @ret_val = @@ROWCOUNT;
- commit transaction
- END TRY
- BEGIN CATCH
- rollback transaction
- PRINT @query_text
- IF ERROR_NUMBER() = 7411
- BEGIN
- PRINT 'MYSQL: ' + @SourceDB + ', ' + @SourceCatalog
- GOTO NEXT_RESULTSET
- END
- ELSE
- BEGIN
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
- PRINT ERROR_MESSAGE()
- CLOSE cur_cfg_sta_import
- DEALLOCATE cur_cfg_sta_import
- return
- END
- END CATCH
- -- Anfrage um zu prüfen, ob die Tabelle bereits im Zielsystem vorhanden ist
- SET @query_text = 'select t.name as table_name from sys.objects o inner join (select object_id, name from sys.objects) t on o.parent_object_id = t.object_id inner join sys.all_columns c on c.object_id = t.object_id where t.name = ''' + @TargetTable + ''''
- set @ret_val = 0
- exec sp_executesql @query_text
- set @ret_val = @@ROWCOUNT;
- if not exists(select * from sta.sys.schemas where name = @TargetSchema)
- begin
- print 'Schema nicht vorhanden'
- declare @schema_query nvarchar(max)
- set @schema_query = 'exec '+ QUOTENAME(@TargetCatalog) + '..sp_executesql N''CREATE SCHEMA [' + @TargetSchema + '] AUTHORIZATION [dbo]'''
- execute(@schema_query)
- end
- -- Erstellen der Tabellenstruktur, wenn nicht vorhanden
- -- Auslesen der Struktur am Linked Server
- -- Erstellen der Tabellen auf diesem Server
- IF (@ret_val > 0)
- begin
- print 'Tabellenstruktur vorhanden'
- end
- else
- begin
- print 'Tabellenstruktur nicht vorhanden'
- DECLARE @SQL_CREATE_TABLE AS NVARCHAR(MAX)
- SET @SQL_CREATE_TABLE = 'CREATE TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ( '
- DECLARE @COLUMN_NAME sysname
- DECLARE @DATA_TYPE sysname
- DECLARE @MAX_LENGTH smallint
- DECLARE @PRECISION tinyint
- DECLARE @SCALE tinyint
- DECLARE @IS_NULLABLE bit
- SET @query_text = 'DECLARE cur_columns CURSOR READ_ONLY
- FOR
- select columns.name as columnname, columns.datatype, columns.max_length,
- columns.precision, columns.scale, columns.is_nullable
- from openquery(' + @SourceDB + ',' +
- '''select distinct c.column_id, c.name, c.max_length, c.precision, c.scale, c.is_nullable, ( select top 1 name from ' + @SourceCatalog + '.sys.types types where c.system_type_id = types.system_type_id) as datatype
- from ' + @SourceCatalog + '.sys.objects o
- inner join (select object_id, name as table_name from '+ @SourceCatalog +'.sys.objects) t on o.parent_object_id = t.object_id and t.table_name = ''''' + @SourceTable + ''''' inner join '+ @SourceCatalog +'.sys.all_columns c on c.object_id = t.object_id order by c.column_id'') columns where columns.datatype != ''sysname'''
- -- Execute the cursor
- exec sp_executesql @query_text
- OPEN cur_columns
- FETCH NEXT
- FROM cur_columns
- INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
- WHILE (@@FETCH_STATUS <> -1)
- BEGIN
- --PRINT 'Constructing DDL statement ...'
- SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '[' + @COLUMN_NAME + '] [' + @DATA_TYPE + ']'
- IF @DATA_TYPE = 'varchar' OR @DATA_TYPE = 'nvarchar' OR @DATA_TYPE = 'char' OR @DATA_TYPE = 'nchar'
- BEGIN
- set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '(' + cast(@MAX_LENGTH / 2 as nvarchar(max)) + ')'
- END
- IF @IS_NULLABLE = 0
- BEGIN
- set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NOT NULL'
- END
- ELSE
- BEGIN
- set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NULL'
- END
- SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ','+ CHAR(10)
- FETCH NEXT
- FROM cur_columns
- INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
- END
- -- Check if we received an empty record set.
- IF NULLIF(@SQL_CREATE_TABLE, '') IS NULL
- begin
- CLOSE cur_columns
- DEALLOCATE cur_columns
- CLOSE cur_cfg_sta_import
- DEALLOCATE cur_cfg_sta_import
- print 'ERROR: No data received from linked server'
- return
- end
- SET @SQL_CREATE_TABLE = CAST (SUBSTRING(@SQL_CREATE_TABLE, 1, LEN(@SQL_CREATE_TABLE)-2) as nvarchar(max)) + ')'
- PRINT 'Done creating DDL statement'
- CLOSE cur_columns
- DEALLOCATE cur_columns
- -- CREATE TABLE
- PRINT 'Executing the following statement' + CHAR(10)+ @SQL_CREATE_TABLE
- BEGIN TRY
- BEGIN TRANSACTION
- exec sp_executesql @SQL_CREATE_TABLE
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- ROLLBACK TRANSACTION
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
- END CATCH
- end
- PRINT 'Erstellen von Primary Keys: START ... '
- SET @query_text =
- '''select STUFF((select distinct '''', '''' + col_.name from ' + @SourceCatalog + '.sys.indexes i
- inner join ' + @SourceCatalog + '.sys.objects t on t.object_id = i.object_id
- inner join ' + @SourceCatalog + '.sys.index_columns idx on idx.index_id = i.index_id
- inner join ' + @SourceCatalog + '.sys.index_columns col on col.object_id = i.object_id
- inner join ' + @SourceCatalog + '.sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
- where is_primary_key = 1 and t.name = ''''' + @SourceTable +
- ''''' FOR XML PATH('''''''')), 1, 2, '''''''') as stuff'''
- SET @query_text =
- '''select STUFF((SELECT
- '''', '''' + c.Name
- FROM ' + @SourceCatalog + '.sys.indexes i
- INNER JOIN ' + @SourceCatalog + '.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
- INNER JOIN ' + @SourceCatalog + '.sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
- INNER JOIN ' + @SourceCatalog + '.sys.objects o ON i.object_id = o.object_id
- INNER JOIN ' + @SourceCatalog + '.sys.schemas sc ON o.schema_id = sc.schema_id
- WHERE i.is_primary_key = 1
- AND o.name = ''''' + @SourceTable +
- '''''
- ORDER BY o.Name, i.Name, ic.key_ordinal
- FOR XML PATH('''''''')), 1, 2, '''''''') as stuff'''
- DECLARE @PK_Columns nvarchar(max);
- SET @query_text = 'select @PK_Columns=(ISNULL(stuff,'''')) from openquery(' + @SourceDB + ', ' + @query_text + ')'
- --print @query_text
- exec sp_executesql @query_text, N'@PK_Columns NVARCHAR(MAX) OUTPUT', @PK_Columns = @PK_Columns OUTPUT
- -- Primary Keys wurden gefunden
- IF @PK_Columns <> ''
- BEGIN
- Print 'Es wurden Primary Keys gefunden'
- DECLARE @PK_Name nvarchar(max)
- SET @PK_Name = ''
- set @query_text = '''select distinct i.name from ' + @SourceCatalog + '.sys.indexes i
- inner join ' + @SourceCatalog + '.sys.objects t on t.object_id = i.object_id
- inner join ' + @SourceCatalog + '.sys.index_columns idx on idx.index_id = i.index_id
- inner join ' + @SourceCatalog + '.sys.index_columns col on col.object_id = i.object_id
- inner join ' + @SourceCatalog + '.sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
- where is_primary_key = 1 and t.name = ''''' + @SourceTable + ''''''''
- set @query_text = 'select @RET_VAL=(name) from openquery (' + @SourceDB + ', ' + @query_text + ')'
- print @query_text
- exec sp_executesql @query_text, N'@RET_VAL NVARCHAR(MAX) OUTPUT', @ret_val = @PK_Name OUTPUT
- SET @query_text = 'ALTER TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ADD CONSTRAINT ' + char(10) +
- @PK_Name + ' PRIMARY KEY NONCLUSTERED ( ' + @PK_Columns + ')'
- --PRINT @query_text
- BEGIN TRY
- BEGIN TRANSACTION
- exec sp_executesql @query_text
- PRINT 'Constaint für ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' -> ' + @PK_Name + ' wurde erstellt'
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- ROLLBACK TRANSACTION
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
- END CATCH
- END
- set @sql = 'INSERT INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' SELECT * FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable + ' ' + @TableFilter
- PRINT @sql
- END
- -- INSERT THE DATA INTO THE DESTINATION TABLE
- --PRINT 'Executing the following statement' + CHAR(10)+ @sql
- BEGIN TRY
- BEGIN TRANSACTION
- EXEC(@sql)
- set @count = @@ROWCOUNT;
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'IMPORT' , CAST(@count as varchar) + ' row(s) affected into Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable ,'Success' as a ;
- PRINT ''
- PRINT CAST(@count as varchar) + ' Zeilen wurden zur Tablle ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' hinzugefügt'
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- ROLLBACK TRANSACTION
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
- END CATCH
- --END
- NEXT_RESULTSET:
- FETCH NEXT FROM cur_cfg_sta_import INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable,@Tablefilter,@USERNAME,@PASSWORD
- END
- CLOSE cur_cfg_sta_import
- DEALLOCATE cur_cfg_sta_import
- insert into CFG_LOG.dbo.LogTable
- ([DateTime],[Funktion],[Text],[Status])
- SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Import von Source nach STA wurde erfolgreich beendet' , 'INFO' as Status ;
Add Comment
Please, Sign In to add comment