· 8 years ago · Oct 25, 2017, 01:00 PM
1
2/* automatic SOURCE to STAR Update */
3
4DECLARE @count int;
5DECLARE @sql nvarchar(max);
6DECLARE @SourceDB as varchar(150);
7DECLARE @SourceCatalog as varchar(150);
8DECLARE @SourceSchema as varchar(150);
9DECLARE @SourceTable as varchar(150);
10DECLARE @SourceQuery as varchar(150);
11DECLARE @TargetDB as varchar(150);
12DECLARE @TargetCatalog as varchar(150);
13DECLARE @TargetSchema as varchar(150);
14DECLARE @TargetTable as varchar(150);
15DECLARE @TableFilter as varchar(500);
16DECLARE @name sysname ;
17DECLARE @PK_ColumnName as varchar(150);
18DECLARE @PK_CONSTRAINT_NAME as varchar(150);
19DECLARE @PK_ID varchar(max)
20
21
22DECLARE @USERNAME as varchar(max)
23DECLARE @PASSWORD as varchar(max)
24
25
26/*************************************************************************/
27/** Logtabelle Copy and Delete **/
28/*************************************************************************/
29EXEC CFG_LOG.dbo.pr_DelUpLog
30
31
32/*************************************************************************/
33/********************** Drop and Insert INTO **********************/
34/*************************************************************************/
35
36INSERT INTO CFG_LOG.dbo.LogTable
37 ([DateTime]
38 ,[Funktion]
39 ,[Text]
40 ,[Status])
41 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Der Import wurde gestartet' , 'INFO' as Status ;
42
43
44DECLARE cur_cfg_sta_import CURSOR READ_ONLY
45FOR
46 SELECT Query
47 ,SourceDB
48 ,SourceCatalog
49 ,SourceSchema
50 ,SourceTable
51 ,TargetDB
52 ,TargetCatalog
53 ,TargetSchema
54 ,TargetTable
55 ,ISNULL(Tablefilter,'')
56 ,LnkUser
57 ,LnkPw
58 FROM cfg_log.dbo.CFG_STA_IMPORT
59 WHERE upper([UPDATE]) = 'J'
60
61
62 OPEN cur_cfg_sta_import
63 FETCH NEXT
64 FROM cur_cfg_sta_import
65 INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable, @Tablefilter, @USERNAME, @PASSWORD
66
67/*
68******** Tablefilter ********
69Delticket : where datediff(mm, proddate, getdate()) between 0 and 2
70Deltickdet : where id in (select id from simma.delticket where datediff(mm, proddate, getdate()) between 0 and 2)
71*****************************
72*/
73
74WHILE (@@FETCH_STATUS <> -1)
75BEGIN
76
77 --BEGIN TRY
78 -- BEGIN TRANSACTION
79 set @name = N'' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
80 IF EXISTS (SELECT * FROM STA.sys.objects WHERE object_id = OBJECT_ID( @name ) AND type in (N'U') )
81 BEGIN
82 IF @TableFilter =''
83 BEGIN
84 set @sql = 'DROP TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
85 EXEC(@sql)
86 PRINT ''
87 PRINT @sql
88 END
89 ELSE
90 BEGIN
91 set @sql = 'DELETE FROM ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ' + @TableFilter
92 EXEC(@sql)
93 PRINT ''
94 PRINT @sql
95 END
96 END
97
98 BEGIN
99 IF @TableFilter = ''
100 BEGIN
101 set @sql = 'SELECT * INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable
102 END
103 ELSE
104 BEGIN
105 declare @query_text nvarchar(max)
106 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 + ''''
107
108 -- Erstellen von Linked Server, wenn nicht vorhanden
109 if not exists(select server_id from sys.servers where name = @SourceDB)
110 begin
111 exec master.dbo.sp_addlinkedserver @server=@SourceDB, @srvproduct=N'SQL Server'
112 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@SourceDB,@useself=N'False',@locallogin=NULL,@rmtuser=@USERNAME,@rmtpassword=@PASSWORD
113 end
114
115 declare @ret_val int
116 set @ret_val = 0
117 exec sp_executesql @query_text
118 set @ret_val = @@ROWCOUNT;
119
120 if not exists(select * from sta.sys.schemas where name = @TargetSchema)
121 begin
122 print 'Schema nicht vorhanden'
123 declare @schema_query nvarchar(max)
124
125 set @schema_query = 'exec '+ QUOTENAME(@TargetCatalog) + '..sp_executesql N''CREATE SCHEMA [' + @TargetSchema + '] AUTHORIZATION [dbo]'''
126 execute(@schema_query)
127 end
128
129 -- Erstellen der Tabellenstruktur, wenn nicht vorhanden
130 -- Auslesen der Struktur am Linked Server
131 -- Erstellen der Tabellen auf diesem Server
132 IF (@ret_val > 0)
133 begin
134 print 'Tabellenstruktur vorhanden'
135 end
136 else
137 begin
138 print 'Tabellenstruktur nicht vorhanden'
139
140 DECLARE @SQL_CREATE_TABLE AS NVARCHAR(MAX)
141 SET @SQL_CREATE_TABLE = 'CREATE TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ( '
142
143 DECLARE @COLUMN_NAME sysname
144 DECLARE @DATA_TYPE sysname
145 DECLARE @MAX_LENGTH smallint
146 DECLARE @PRECISION tinyint
147 DECLARE @SCALE tinyint
148 DECLARE @IS_NULLABLE bit
149
150 SET @query_text = 'DECLARE cur_columns CURSOR READ_ONLY
151 FOR
152 select columns.name as columnname, columns.datatype, columns.max_length,
153 columns.precision, columns.scale, columns.is_nullable
154 from openquery(' + @SourceDB + ',' +
155 '''select distinct c.column_id, c.name, c.max_length, c.precision, c.scale, c.is_nullable, ( select top 1 name from ' + @TargetSchema + '.sys.types types where c.system_type_id = types.system_type_id) as datatype
156 from ' + @TargetSchema + '.sys.objects o
157 inner join (select object_id, name as table_name from '+ @TargetSchema +'.sys.objects) t on o.parent_object_id = t.object_id and t.table_name = ''''' + @TargetTable + ''''' inner join '+ @TargetSchema +'.sys.all_columns c on c.object_id = t.object_id order by c.column_id'') columns where columns.datatype != ''sysname'''
158
159 -- Execute the cursor
160 exec sp_executesql @query_text
161
162 OPEN cur_columns
163 FETCH NEXT
164 FROM cur_columns
165 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
166
167 WHILE (@@FETCH_STATUS <> -1)
168 BEGIN
169 --PRINT 'Constructing DDL statement ...'
170
171 SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '[' + @COLUMN_NAME + '] [' + @DATA_TYPE + ']'
172
173 IF @DATA_TYPE = 'varchar' OR @DATA_TYPE = 'nvarchar' OR @DATA_TYPE = 'char' OR @DATA_TYPE = 'nchar'
174 BEGIN
175 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + '(' + cast(@MAX_LENGTH / 2 as nvarchar(max)) + ')'
176 END
177
178 IF @IS_NULLABLE = 0
179 BEGIN
180 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NOT NULL'
181 END
182 ELSE
183 BEGIN
184 set @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ' NULL'
185 END
186
187 SET @SQL_CREATE_TABLE = @SQL_CREATE_TABLE + ','+ CHAR(10)
188
189 FETCH NEXT
190 FROM cur_columns
191 INTO @COLUMN_NAME,@DATA_TYPE, @MAX_LENGTH, @PRECISION, @SCALE, @IS_NULLABLE
192 END
193
194 -- Check if we received an empty record set.
195 IF NULLIF(@SQL_CREATE_TABLE, '') IS NULL
196 begin
197 CLOSE cur_columns
198 DEALLOCATE cur_columns
199 CLOSE cur_cfg_sta_import
200 DEALLOCATE cur_cfg_sta_import
201 print 'ERROR: No data received from linked server'
202 return
203 end
204
205 SET @SQL_CREATE_TABLE = CAST (SUBSTRING(@SQL_CREATE_TABLE, 1, LEN(@SQL_CREATE_TABLE)-2) as nvarchar(max)) + ')'
206 PRINT 'Done creating DDL statement'
207
208 CLOSE cur_columns
209 DEALLOCATE cur_columns
210
211 -- CREATE TABLE
212 PRINT 'Executing the following statement' + CHAR(10)+ @SQL_CREATE_TABLE
213 BEGIN TRY
214 BEGIN TRANSACTION
215 exec sp_executesql @SQL_CREATE_TABLE
216 COMMIT TRANSACTION
217 END TRY
218 BEGIN CATCH
219 ROLLBACK TRANSACTION
220 insert into CFG_LOG.dbo.LogTable
221 ([DateTime],[Funktion],[Text],[Status])
222 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
223 END CATCH
224 end
225
226
227 SET @query_text = 'select * from openquery(' + @SourceDB + ', select distinct i.name as PKName, col_.name as ColName from sys.indexes i
228 inner join sys.objects t on t.object_id = i.object_id
229 inner join sys.index_columns idx on idx.index_id = i.index_id
230 inner join sys.index_columns col on col.object_id = i.object_id
231 inner join sys.columns col_ on col_.column_id = col.column_id and col_.object_id = i.object_id
232 where is_primary_key = 1 and t.name = ' + '''' + @TargetSchema + ''' FOR XML PATH('''')), 1, 2, '''')'')';
233
234 DECLARE @PK_Columns nvarchar(max)
235 SET @PK_Columns = ''
236
237 DECLARE @PKName nvarchar(max)
238 SET @PKName = ''
239
240
241
242 PRINT @Query_Text
243 --exec(@query_text)
244
245
246
247 SET @query_text = 'ALTER TABLE ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' ADD CONSTRAINT ' + char(10) +
248 ' PRIMARY KEY NONCLUSTERED ( ' + ')'
249
250
251 SET @sql = ''
252
253 set @sql = 'INSERT INTO ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable + ' SELECT * FROM ' + @SourceDB + '.' + @SourceCatalog + '.' + @SourceSchema + '.' + @SourceTable + ' ' + @TableFilter
254 PRINT @sql
255 END
256 -- INSERT THE DATA INTO THE DESTINATION TABLE
257 PRINT 'Executing the following statement' + CHAR(10)+ @sql
258 BEGIN TRY
259 BEGIN TRANSACTION
260 EXEC(@sql)
261 set @count = @@ROWCOUNT;
262 insert into CFG_LOG.dbo.LogTable
263 ([DateTime],[Funktion],[Text],[Status])
264 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , CAST(@count as varchar) + ' row(s) affected into Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable ,'Success' as a ;
265 PRINT ''
266 PRINT CAST(@count as varchar) + ' row(s) affected into Table ' + @TargetCatalog + '.' + @TargetSchema + '.' + @TargetTable
267 COMMIT TRANSACTION
268 END TRY
269 BEGIN CATCH
270 ROLLBACK TRANSACTION
271 insert into CFG_LOG.dbo.LogTable
272 ([DateTime],[Funktion],[Text],[Status])
273 SELECT SYSDATETIME() as ZEIT, 'ErrorHandling' , ERROR_MESSAGE() + ' ErrorNumber' + CAST(ERROR_NUMBER()as VARCHAR(4)) as Error, 'Error' as a ;
274 END CATCH
275 END
276 FETCH NEXT FROM cur_cfg_sta_import INTO @SourceQuery,@SourceDB, @SourceCatalog, @SourceSchema, @SourceTable, @TargetDB, @TargetCatalog, @TargetSchema, @TargetTable,@Tablefilter,@USERNAME,@PASSWORD
277END
278
279CLOSE cur_cfg_sta_import
280DEALLOCATE cur_cfg_sta_import
281
282insert into CFG_LOG.dbo.LogTable
283 ([DateTime],[Funktion],[Text],[Status])
284 SELECT SYSDATETIME() as ZEIT, 'IMPORT' , 'Import von Source nach STA wurde erfolgreich beendet' , 'INFO' as Status ;