/*  automatic SOURCE to STAR Update */
-- CFG_STA_IMPORT

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 'ERROR: ' + @SourceDB + ', ' + @SourceCatalog + '; ' + ERROR_MESSAGE()

							--PRINT @query_text
							IF ERROR_NUMBER() = 7411
							BEGIN
								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 ;

								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 DDL to create Table ' +  @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
							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
						 '''', '''' + 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
								PRINT 'FEHLER beim Erstellen der Constaint für ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' -> ' + @PK_Name
								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 ;