i have 2 tables. table has 380 columns , 360 columns created based on row value particular column in table_b. add new row table_b , if not column in table_a new column needs created in table_a.
the columns in table_a calculated based on logic other column in same table , table table_c.
this query below takes hour , 30 minutes complete.
is there other optimal solution situation?
i have following query takes hour , half complete.
declare @@columnname varchar(25) declare column_cursor cursor select distinct [column table_a] [dbo].table_b open column_cursor fetch next column_cursor @columnname while @@fetch_status = 0 begin set @starttime = getdate() if not exists (select * sys.columns name = @columnname , object_id = object_id('[table_a]')) begin select @message =isnull(@columnname,'null column') +' doesn''t exists, adding one' print @message set @sqlquery ='alter table [dbo].[table_a] add ['+@columnname+'] decimal(20,5)' execute sp_executesql @sqlquery end set @sqlquery ='update [table_a] set ['+@columnname+'] = [amount] [value] = '''+@columnname+''' update [table_a] set ['+@columnname+'] = (select sum(total_au) totalusd [table_c] p [unique id ic] = concat([unique id],''-'','''+@columnname+''') group p.[unique id] ) [value] <> '''+@columnname+''' , ( ['+@columnname+'] = 0 or ['+@columnname+'] null)' execute sp_executesql @sqlquery set @endtime = getdate() set @elaspedtime =datediff(second, @starttime, @endtime) print right('0' + cast(@elaspedtime / 3600 varchar),2) + ':' + right('0' + cast((@elaspedtime / 60) % 60 varchar),2) + ':' + right('0' + cast(@elaspedtime % 60 varchar),2) fetch next column_cursor @columnname end close column_cursor deallocate column_cursor
Comments
Post a Comment