批量替换数据表里的数据

SET NOCOUNT ON

DECLARE @TName varchar(100) --TableName

DECLARE @FieldName varchar(100) --ReplaceFieldName

DECLARE @OldKeyVale varchar(max)

DECLARE @ReplaceKeyValue varchar(max)

SET @TName ='tbl_ddl_log'

SET @FieldName = 'TSQL'

SET @OldKeyVale = 'TesttableReplace'

SET @ReplaceKeyValue ='Testtable'

DECLARE @SQL varchar(max)

SET @SQL = ' DECLARE @KeyFrontValue nvarchar(max) DECLARE @KeyBehindValue nvarchar(max)

DECLARE @T TABLE(RowNum int,OldKeyValue nvarchar(max)) INSERT INTO @T SELECT Row_Number()over(ORDER BY [#FieldName#]),[#FieldName#] FROM #TNameTemp# IF(EXISTS(SELECT * FROM #TNameTemp#)) BEGIN DECLARE @MaxRowNumber int,@DoNum int,@MinRowNumber int DECLARE @NewKeyValueAll nvarchar(max) DECLARE @OldKeyValueAll nvarchar(max) SELECT @MaxRowNumber=MAX(RowNum) FROM @T SELECT @MinRowNumber=MIN(RowNum) FROM @T SET @DoNum =0 WHILE(@MinRowNumber<= @MaxRowNumber) BEGIN DECLARE @iCount_KeyID int SELECT @iCount_KeyID = CHARINDEX(''#OldKeyTemp#'',OldKeyValue) FROM @T WHERE RowNum = @MinRowNumber IF(@iCount_KeyID > 0) BEGIN SELECT @NewKeyValueAll =REPLACE(OldKeyValue,''#OldKeyTemp#'',''#ReplaceKeyTemp#''),@OldKeyValueAll = OldKeyValue FROM @T WHERE RowNum = @MinRowNumber UPDATE #TNameTemp# SET [#FieldName#]= @NewKeyValueAll WHERE [#FieldName#] = @OldKeyValueAll SET @DoNum = 1 IF(@@ERROR<>0) PRINT ''Replace Fail'' ELSE PRINT ''Replace Successful'' END SET @MinRowNumber = @MinRowNumber + 1 END IF(@DoNum =0) print ''no accord row in the table'' END else print ''no row in the table'''

SET @SQL = Replace(@SQL,'#TNameTemp#',@TName)

SET @SQL = Replace(@SQL,'#FieldName#',@FieldName)

SET @SQL = Replace(@SQL,'#OldKeyTemp#',@OldKeyVale)

SET @SQL = Replace(@SQL,'#ReplaceKeyTemp#',@ReplaceKeyValue)

PRINT @SQL

SET NOCOUNT OFF

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7192349/viewspace-1001874/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7192349/viewspace-1001874/