hi can any one help me please ive written this script to update dynamically but none of the updates work .. does any one know here is the code
thanks in advance
declare @maxCount int
declare @minCount int
declare @innerloopmincount int
declare @innerloopmaxcount int
declare @tablename varchar(200)
declare @comparisonValue varchar(200)
declare @currentColumn varchar(200)
declare @mastercolmap varchar(200)
declare @duplicatecolmap varchar(200)
--declare @masterV varchar(200)
--declare @DuplicateV varchar(200)
select @maxCount = count(*) from #tablenames
set @minCount = 0
while @mincount < @maxcount
begin
print 'outer loop'
select top 1 @tablename = tablename from #tablenames where checked = 0
print @tablename
select @innerloopmaxcount = count(*) from #columnnames
set @innerloopmincount = 0
while @innerloopmincount < @innerloopmaxcount
begin
print 'inerloop'
select @comparisonValue = comparisoncol from #TableNames where tablename = + @tablename
select top 1 @currentColumn = colname from #columnnames where checked = 0
print @currentColumn
select @mastercolmap = mastercolmap from #TableNames where tablename =@tablename
select @duplicatecolmap = duplicatecolmap from #TableNames where tablename =@tablename
declare @sql nvarchar(4000)
set @sql = 'declare @masterV varchar(2000)'
+ ' Declare @Duplicatev varchar(2000)'
+ ' declare @minloopVal int'
+ ' declare @maxloopval int'
+ ' declare @Mval nvarchar(200)'
+ ' declare @dval nvarchar(200)'
+ ' declare @goto_v varchar(200)'
+ ' set @minloopval = 0'
+ ' select @maxloopval = count(*) from dupes'
+ ' update dupes set checked = 0'
+ ' while @minloopval < @maxloopval'
+ ' begin'
+ ' print @minloopval'
+ ' select @goto_v = colgoto from #columnnames where colname = ' + '''' + @currentColumn + ''''
+ ' select top 1 @masterV =' + @mastercolmap + ' from dupes where checked != 1 '
+ ' select top 1 @Duplicatev=' + @duplicatecolmap + ' from dupes where checked != 1'
+ ' select @masterV = convert(uniqueidentifier,@masterV)'
+ ' select @Duplicatev = convert(uniqueidentifier,@Duplicatev)'
+ ' print @masterV'
+ ' if @goto_v = 10 '
+ ' begin'
+ ' select @Mval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@masterv'
+ ' select @dval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@Duplicatev'
+ ' select @mval = isnull(@mval,''0'')'
+ ' select @dval = isnull(@dval,''0'')'
+ ' if @mval >= @dval or @mval = 1'
+ ' begin'
+ ' print ''no action taken'''
+ ' end'
+ ' else'
+ ' begin'
+ ' update ' + @tablename + ' set ' + @currentColumn + '= @dval where ' + @comparisonValue + ' = @masterv '
+ ' end '
+ ' end '
+ ' update dupes set checked = 1 where ' + @mastercolmap + '= @masterv'
+ ' delete from ' + @tablename + ' where ' + @comparisonValue + '= @Duplicatev'
+ ' set @minloopval = @minloopval + 1'
+ ' end'
exec sp_executesql @sql
update #columnnames set checked ='1' where colname = @currentColumn
set @innerloopmincount = @innerloopmincount + 1
end
update #tablenames set checked ='1' where tablename = @tablename
set @mincount = @mincount + 1
end
thanks in advance
declare @maxCount int
declare @minCount int
declare @innerloopmincount int
declare @innerloopmaxcount int
declare @tablename varchar(200)
declare @comparisonValue varchar(200)
declare @currentColumn varchar(200)
declare @mastercolmap varchar(200)
declare @duplicatecolmap varchar(200)
--declare @masterV varchar(200)
--declare @DuplicateV varchar(200)
select @maxCount = count(*) from #tablenames
set @minCount = 0
while @mincount < @maxcount
begin
print 'outer loop'
select top 1 @tablename = tablename from #tablenames where checked = 0
print @tablename
select @innerloopmaxcount = count(*) from #columnnames
set @innerloopmincount = 0
while @innerloopmincount < @innerloopmaxcount
begin
print 'inerloop'
select @comparisonValue = comparisoncol from #TableNames where tablename = + @tablename
select top 1 @currentColumn = colname from #columnnames where checked = 0
print @currentColumn
select @mastercolmap = mastercolmap from #TableNames where tablename =@tablename
select @duplicatecolmap = duplicatecolmap from #TableNames where tablename =@tablename
declare @sql nvarchar(4000)
set @sql = 'declare @masterV varchar(2000)'
+ ' Declare @Duplicatev varchar(2000)'
+ ' declare @minloopVal int'
+ ' declare @maxloopval int'
+ ' declare @Mval nvarchar(200)'
+ ' declare @dval nvarchar(200)'
+ ' declare @goto_v varchar(200)'
+ ' set @minloopval = 0'
+ ' select @maxloopval = count(*) from dupes'
+ ' update dupes set checked = 0'
+ ' while @minloopval < @maxloopval'
+ ' begin'
+ ' print @minloopval'
+ ' select @goto_v = colgoto from #columnnames where colname = ' + '''' + @currentColumn + ''''
+ ' select top 1 @masterV =' + @mastercolmap + ' from dupes where checked != 1 '
+ ' select top 1 @Duplicatev=' + @duplicatecolmap + ' from dupes where checked != 1'
+ ' select @masterV = convert(uniqueidentifier,@masterV)'
+ ' select @Duplicatev = convert(uniqueidentifier,@Duplicatev)'
+ ' print @masterV'
+ ' if @goto_v = 10 '
+ ' begin'
+ ' select @Mval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@masterv'
+ ' select @dval =' + @currentColumn + ' from ' + @tablename + ' where '+ @comparisonValue + '=@Duplicatev'
+ ' select @mval = isnull(@mval,''0'')'
+ ' select @dval = isnull(@dval,''0'')'
+ ' if @mval >= @dval or @mval = 1'
+ ' begin'
+ ' print ''no action taken'''
+ ' end'
+ ' else'
+ ' begin'
+ ' update ' + @tablename + ' set ' + @currentColumn + '= @dval where ' + @comparisonValue + ' = @masterv '
+ ' end '
+ ' end '
+ ' update dupes set checked = 1 where ' + @mastercolmap + '= @masterv'
+ ' delete from ' + @tablename + ' where ' + @comparisonValue + '= @Duplicatev'
+ ' set @minloopval = @minloopval + 1'
+ ' end'
exec sp_executesql @sql
update #columnnames set checked ='1' where colname = @currentColumn
set @innerloopmincount = @innerloopmincount + 1
end
update #tablenames set checked ='1' where tablename = @tablename
set @mincount = @mincount + 1
end