Here, I am sharing SQL Procedure using which you can bulk update records just in one go by using SQL Split, Parsename and CROSS APPLY. Let’s see. 

  

  Declare @values varchar(MAX)='1|150|Pb|1,2|201|Hr|1,3|900|Dl|1,4|1250|Up|0' 

  @output int OUTPUT 

  BEGIN 

  WITH cte 

  AS ( 

SELECT Replace(Rtrim(Ltrim(split.a.value('.', 'VARCHAR(100)'))), '|','.') 

AS split_data FROM   (SELECT Cast ('' + Replace(@values, ',', '') 

+ '' AS XML) AS Data) AS A 

CROSS apply data.nodes ('/M') AS Split(a)) 

SELECT 

       Id = Parsename(split_data, 4), 

       Plot=Parsename(split_data, 3), 

       State = Parsename(split_data, 2), 

       IsSold = Parsename(split_data, 1) 

INTO #tbl FROM   cte 

  

Update arm SET ivt.Plot=  t.Plot, ivt.State = t.State , 
ivt.IsSold = t.IsSold 

From 

Inventory ivtinner join #tbl t on 

ivt.ID = t.Id 

  

IF OBJECT_ID('tempdb..#tbl') IS NOT NULL  

       Drop table #tbl 

SET @output=1 

END 

  

About Parsename: ParsenameReturns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name. 

Explanation: Here we are delimiting Input value parameters with pipeline delimiter. Adding XML tags in select and then splitting the values within CTE and assigning the values to temporary table. Using the temporary table in Update query joining with temporary table accomplishes our desired result. All these records gets updated just in one go. 

NOTE: This logic holds true when we are updating limited number of columns. You might need to modify the logic if you plan to update many number of columns. 

blog comments powered by Disqus