2008/03/03

Another way to bulk update data in SQL (Updated)

In a small application, sometimes we need to update some among of data in database frequently. I ever mentioned how to convert xml into a table in SQL, so that we can convert those data into a XML file and send it to SQL server.
What if those data that need to be updated contain many duplicate columns? We can send those duplicate columns just once, and concatenate those non-duplicate columns into a string and send it to SQL as a parameter. It works like this:
1.create a method to fetch data from List and merge them into a string by using a delimiter to seperate them.
For example:
//This is a new feature of C# 3.0. Take a look of this:
//"http://blogs.msdn.com/abhinaba/archive/2005/09/17/470358.aspx".
List<int> CustomerIdList = new List<int>{1001,1002,1003,1004};
string CustomerIds = MergeData(CustomerIdList);
Create your own MergeData() method to merge the input List<int> and output as a single string like this: "1001,1002,1003,1004".
2.Send this string as a parameter to the SQL server.
3.At SQL server, we need to create a Table-Valued function to parse this string into a table.
CREATE FUNCTION [dbo].[fxnParseCommaDelmitedList]
(
@CommaDelimitedList varchar(8000)
)
RETURNS @TableVar TABLE (ItemID int NOT NULL )
AS
BEGIN
  DECLARE @IDListPosition int
  DECLARE @IDList varchar(4000)
  DECLARE @ArrValue varchar(4000)
  SET @IDList = COALESCE(@CommaDelimitedList, '')
  IF @IDList <> ''
  BEGIN
  -- Add comma to end of list so user doesn''t have to
  SET @IDList = @IDList + ','
  -- Loop through the comma demlimted string list
  WHILE PATINDEX('%,%' , @IDList ) <> 0
    BEGIN
      SELECT @IDListPosition = PATINDEX('%,%' , @IDList)
      SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)
      -- Insert parsed ID into TableVar for "where in select"
      INSERT INTO @TableVar (ItemID) VALUES (CONVERT(int, @ArrValue))
      -- Remove processed string
      SELECT @IDList = STUFF(@IDList, 1, @IDListPosition, '')
    END
  END
  RETURN
END

This sproc will return a table like this:
ItemID (column name)
1001
1002
1003
1004

A lighter way than using XML file.