Re: Rename all the Table in the database at once

Declare test cursor for select name From sysobjects where name like '%[NEW]' and type='u'
open test
declare @name sysname
declare @newname sysname
declare @str varchar(1000)
declare @newstr varchar(1000)
set @str='sp_rename @name,@newname'
fetch next from test into @name
while @@Fetch_Status=0
begin
print @name
select @newname=@name+'_NEW'   --left(@name, len(@name)-1)
print @newname
select @newstr=replace(@str,'@name',@name)
select @newstr=replace(@newstr,'@newname',@newname)
print @newstr
exec (@newstr)
fetch next from test into @name
end
close test
deallocate test

Merge Command

MERGE INTO [TITEMMASTER] AS TARGET
USING(SELECT DISTINCT ITEM,[Specfication],[PACK SIZE],RATE,[purchase Unit],[Category],[SubCategory],[Our Reference],[Local Allowed] FROM [TITEMMASTER_Centre]) as NEW
on LTRIM(RTRIM(TARGET.itemname))=LTRIM(RTRIM(NEW.item))
AND [SPEC]=NEW.[Specfication]
AND [Pack_Size]=NEW.[Pack Size]
AND [UOM]=NEW.[purchase Unit]
WHEN MATCHED AND
(
--TARGET.[ItemName]<>NEW.[Item]
TARGET.[Amount]<>NEW.[Rate] OR
TARGET.[Category]<>NEW.[Category] OR
TARGET.[Sub_Category]<>NEW.[SubCategory] OR
TARGET.[PurchaseType]<>NEW.[Our Reference]
)
THEN UPDATE SET
TARGET.[ItemName]=NEW.[Item],
TARGET.[SPEC]=NEW.[Specfication],
TARGET.[Pack_Size]=NEW.[Pack Size],
TARGET.[Amount]=NEW.[Rate],
TARGET.[UOM]=NEW.[purchase Unit],
TARGET.[Category]=NEW.[Category],
TARGET.[Sub_Category]=NEW.[SubCategory],
TARGET.[PurchaseType]=NEW.[Our Reference],
TARGET.[UpdatedDate]=GETDATE()

WHEN NOT MATCHED BY TARGET 
THEN INSERT
(
[ItemName],
[SPEC],
[Pack_Size],
[Amount],
[UOM],
[Category],
[Sub_Category],
[PurchaseType],
[ItemType],
[InsertedDate],
[UpdatedDate],
[OBSOLETE]
)
VALUES
(
[Item],
[Specfication],
[Pack Size],
[Rate],
[purchase Unit],
[Category],
[SubCategory],
[Our Reference],
'S',
GETDATE(),
GETDATE(),
'0'
);