As the support couldn't help us anymore with SQL I hope someone of you can help me.
In our database we have (because of a W7 Release) several data sets with double information - so the same script from the old XP robot and from the new W7 robot (watch the attachment). Because of our dashboard we must merge these data. All old XP robot entries should get the new W7 robot name (all webservices also need the new source name which is not neccessary for the e2e_appmon scripts).
Of the support we got a SQL statement as the merge function of the tools would need too much time for the bunch of scripts we have to merge.
DECLARE @qos varchar(255), @source varchar(255), @tableid int, @tableid2 int, @rtable varchar(255), @query varchar(255), @target varchar(255); DECLARE @sname varchar(255), @lname varchar(255); -- Put longname QoS-es into temporary table (this will be deleted) select qos, source, table_id, r_table, target INTO #TempQoSTable from S_QOS_DATA where probe = 'e2e_appmon' and source = 'DE_HH_X1SLM57' and target like 'Test_SQL%'; -- cursor for the QoS series we/d like to keep (names as per october 15th) DECLARE q CURSOR FOR select qos, source, target, table_id, r_table from S_QOS_DATA where probe = 'e2e_appmon' and source = 'DE_AC_X2SLM66_ENTWJD' and target like 'Test_SQL%' OPEN q FETCH NEXT FROM q INTO @qos, @source, @target, @tableid, @rtable WHILE @@FETCH_STATUS = 0 BEGIN -- Compare names - fetch table_id for all QoS-es starting with shortname plus period select @tableid2 = table_id from #TempQoSTable where target = @target and qos = @qos if (@tableid2 IS NULL) begin -- Skip if not found CONTINUE; end else begin -- Merge QoS series in the RN table select @query = 'update ' + @rtable + ' set table_id = ' + CAST(@tableid as varchar) + ' where tableid = ' + CAST(@tableid2 as varchar) -- select to check, replace with exec to run exec (@query) -- Delete extra row from S_QOS_DATA select @query = 'delete from S_QOS_DATA where table_id = ' + CAST(@tableid2 as varchar) -- select to check, replace with exec to run exec (@query) -- Delete extra row from temp table select @query = 'delete from #TempQoSTable where table_id = ' + CAST(@tableid2 as varchar) -- select to check, replace with exec to run exec (@query) end FETCH NEXT FROM q INTO @qos, @source, @target, @tableid, @rtable END CLOSE q; DEALLOCATE q; DROP TABLE #TempQoSTable
Sadly I have some problems with it and the support coulnd`t help anymore.
First of all I use the program SQuirreL as it`s the only free program which I found that can execute multi sql statements (which the above statement is). But when I try to execute this statement it`s like a loop as the execution needs really long (for testing I just used some data sets with just a few measurements...not more than 10).
Maybe the program isn´t good? Or is there any mistake in the statement?
Thank you for your help in advance.