AnsweredAssumed Answered

Merge Data

Question asked by sd-servicemon on Mar 4, 2014
Latest reply on Aug 23, 2016 by bhupesh_walde

Hello everyone.

 

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.

 

Kind Regards

Jessica 

Attachments

Outcomes