March 20, 2013 at 5:58 am
Hi,
I have two tables.
treatment_plan table having 8 fields like txt_instruct_Description_1,txt_instruct_Description_2....
As per requirement txt_instruct_Description_4,txt_instruct_Description_5,txt_instruct_Description_6,txt_instruct_Description_7,
txt_instruct_Description_8 field values should be moved to another table custom_plan.
custom_plan table having ,txt_addition_description_1,txt_addition_description_2,txt_addition_description_3,
txt_addition_description_4,txt_addition_description_5,txt_addition_description_6,txt_addition_description_7,
txt_addition_description_8 fields.
I want to move treatment_plan table 5 fields value to custom_plan 8 fields.
I have written the following the query.
But this query taking more time to execute.
Is there any other way to improve the performance or changing the code to execute fast. Please let me know.
-------------------------------------------------
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM SYSOBJECTS SO WHERE SO.NAME = 'custom_plan')
IF EXISTS (SELECT 1 FROM SYSOBJECTS SO WHERE SO.NAME = 'treatment_plan')
BEGIN
declare @num_source_field int
declare @num_destination_field int
declare @source_field varchar(100)
declare @destination_field varchar(100)
declare @src_value varchar(75)
declare @dest_value varchar(75)
declare @strsql varchar(1000)
SET @num_source_field = 4
SET @num_destination_field = 1
select *, id = IDENTITY(INT,1,1) into #temp from treatment_plan
Declare @mx int
declare @mn int
Declare @encid varchar(45)
select @mx=max(id),@mn=min(id) from #temp
create table #tbl(col1 varchar(45))
while(@mn<=@mx)
BEGIN
select @encid= enc_id from #temp where id=@mn
SET @num_source_field=4
while(@num_source_field <= 8)
BEGIN
select @source_field = 'txt_instruct_description_'+cast(@num_source_field as varchar(2))
SET @num_destination_field = 1
while(@num_destination_field <= 8)
BEGIN
select @destination_field = 'txt_additional_description_'+cast(@num_destination_field as varchar(2))
truncate table #tbl
SET @strsql='insert into #tbl select '+@source_field+' from treatment_plan where enc_id='+''''+@encid +''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
select @src_value= col1 from #tbl
truncate table #tbl
SET @strsql='insert into #tbl select '+@destination_field+' from custom_plan where enc_id='+''''+@encid +''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
select @dest_value= col1 from #tbl
if(@dest_value is null)
begin
SET @strsql='update custom_plan SET '+@destination_field+'='+''''+@src_value+''''+' where enc_id='+''''+@encid+''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
break
end
SET @num_destination_field=@num_destination_field+1
END
SET @num_source_field=@num_source_field+1
END
SET @mn=@mn+1
END
drop table #tbl
drop table #temp
END
Print '----------End----------'
SET NOCOUNT OFF
GO
--------------------------------------------------------
Thanks,
Tony
March 20, 2013 at 7:38 am
Solution is doing it row-by-row, no surprise that's painfull.
Why not design a set based solution? A single insert into select from statement should do it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 20, 2013 at 10:25 pm
Thanks for your comments.
If am going with While loop option, client is rejecting because they need good performance query.
How can i set this query like set based solution please let me know.
How can i make this query to use a single insert into select from statement, Could you Please suggest me in detail.
thanks,
Antony
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply