To Split the String Value

SET NOCOUNT ON;
GO

Declare @data varchar(100) = 'MDEB KLP 657 678989'
Declare @date varchar(100) = '2012-5-06'

DECLARE @T TABLE ([description] varchar(MAX));

INSERT INTO @T ([description]) VALUES (@data)

Insert into [TblEBMeterReading]

SELECT
 @date as Date,
 --Q.x.value('(/e/text())[1]', 'varchar(MAX)') [Category],
 Q.x.value('(/e/text())[2]', 'varchar(25)') CenterCode,
 Q.x.value('(/e/text())[3]', 'int') MeterNo,
 Q.x.value('(/e/text())[4]', 'int') MeterReading
FROM
 @T AS T
 CROSS APPLY
 (SELECT T.[description] AS [text()] FOR XML PATH('')) AS R(s)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(R.s, ' : ', '</e><e>') + '</e>' AS xml)) AS S(x)
 CROSS APPLY
 S.x.nodes('/e') AS N1(e)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(REPLACE(N1.e.value('text()[1]', 'varchar(MAX)'), ' ', '</e><e>'), ' ', '</e><e>') + '</e>' AS xml)) AS Q(x)
GO

StoredProcedure used in Jobs

select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%storeproce%'

To Split the column values

Table1 
Value   String 
1          Cleo, Smith 

 

Select

 Value  ,

 Substring(FullName, 1,Charindex(',', FullName)-1) as Name, 
 Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as  Surname 
from Table1

 

 

Output 
Value  Name   Surname 
1        Cleo     Smith 

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'
);