Passing multiple rows of data to a stored procedure

Question

I have a list of objects (created from several text files) in C#.net that I need to store in a SQL2005 database file. Unfortunately, Table-Valued Parameters began with SQL2008 so they won't help. I found from MSDN that one method is to "Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement" but I am rather new to stored procedures and need more help than that. I know I could create a stored procedure to create one record then loop through my list and add them, but that's what I'm trying to avoid. Thanks.

Input file example (Other files contain pricing and availability):
Matnr   ShortDescription    LongDescription ManufPartNo Manufacturer    ManufacturerGlobalDescr GTIN    ProdFamilyID    ProdFamily  ProdClassID ProdClass   ProdSubClassID  ProdSubClass    ArticleCreationDate CNETavailable   CNETid  ListPrice   Weight  Length  Width   Heigth  NoReturn    MayRequireAuthorization EndUserInformation  FreightPolicyException
10000000    A&D ENGINEERING SMALL ADULT CUFF FOR UA-767PBT  UA-279  A&D ENGINEERING A&D ENG 093764011542    GENERAL General TDINTERNL   TD Internal TDINTERNL   TD Internal 2012-05-13 12:18:43 N       18.000  .350                N   N   N   N
10000001    A&D ENGINEERING MEDIUM ADULT CUFF FOR UA-767PBT UA-280  A&D ENGINEERING A&D ENG 093764046070    GENERAL General TDINTERNL   TD Internal TDINTERNL   TD Internal 2012-05-13 12:18:43 N       18.000  .450                N   N   N   N

Some DataBase File fields:

EffectiveDate           varchar(50)
MfgName                 varchar(500)
MfgPartNbr              varchar(500)
Cost                    varchar(200)
QtyOnHand               varchar(200)

Answer 1

You can split multiple values from a single string quite easily. Say you can bundle the string like this, using a comma to separate "columns", and a semi-colon to separate "rows":

foo, 20120101, 26; bar, 20120612, 32

(This assumes that colons and semi-colons can't appear naturally in the data; if they can, you'll need to choose other delimiters.)

You can build a split routine like this, which includes an output column that allows you to determine the order the value appeared in the original string:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

Then you can query it like this (for simplicity and illustration I'm only handling 3 properties but you can extrapolate this for 11 or n):

DECLARE @x NVARCHAR(MAX); -- a parameter to your stored procedure

SET @x = N'foo, 20120101, 26; bar, 20120612, 32';

;WITH x AS 
(
    SELECT ID = s.Number, InnerID = y.Number, y.Item 
    -- parameter and "row" delimiter here:
    FROM dbo.SplitStrings(@x, ';') AS s
    -- output and "column" delimiter here:
    CROSS APPLY dbo.SplitStrings(s.Item, ',') AS y
)
SELECT 
    prop1 = x.Item, 
    prop2 = x2.Item, 
    prop3 = x3.Item
FROM x 
INNER JOIN x AS x2 
ON x.InnerID = x2.InnerID - 1
AND x.ID = x2.ID
INNER JOIN x AS x3
ON x2.InnerID = x3.InnerID - 1
AND x2.ID = x3.ID
WHERE x.InnerID = 1
ORDER BY x.ID;

Results:

prop1   prop2     prop3
------  --------  -------
foo     20120101  26
bar     20120612  32

Answer 2

We use XML data types like this...

declare @contentXML xml
set @contentXML=convert(xml,N'<ROOT><V a="124694"/><V a="124699"/><V a="124701"/></ROOT>')

SELECT  content_id,
  FROM  dbo.table c WITH (nolock) 
  JOIN @contentXML.nodes('/ROOT/V') AS R ( v ) ON c.content_id = R.v.value('@a', 'INT')

Here is what it would look like if calling a stored procedure...

DbCommand dbCommand = database.GetStoredProcCommand("MyStroredProcedure);
database.AddInParameter(dbCommand, "dataPubXML", DbType.Xml, dataPublicationXml);

CREATE PROC dbo.usp_get_object_content
(
    @contentXML XML
)
AS 
BEGIN
    SET NOCOUNT ON

    SELECT  content_id,
      FROM    dbo.tblIVContent c WITH (nolock) 
      JOIN @contentXML.nodes('/ROOT/V') AS R ( v ) ON c.content_id = R.v.value('@a', 'INT')

 END

SQL Server does not parse XML very quickly so the use of the SplitStrings function might be more performant. Just wanted to provide an alternative.

Answer 3

I can think of a few options, but as I was typing one of them (the Split option) was posted by Mr. @Bertrand above. The only problem with it is that SQL just isn't that good at string manipulation.

So, another option would be to use a #Temp table that your sproc assumes will be present. Build dynamic SQL to the following effect:

Start a transaction, CREATE TABLE #InsertData with the shape you need, then loop over the data you are going to insert, using INSERT INTO #InsertData SELECT <values> UNION ALL SELECT <values>....

There are some limitations to this approach, one of which is that as the data set becomes very large you may need to split the INSERTs into batches. (I don't recall the specific error I got when I learned this myself, but for very long lists of values I have had SQL complain.) The solution, though, is simple: just generate a series of INSERTs with a smaller number of rows each. For instance, you might do 10 INSERT SELECTs with 1000 UNION ALLs each instead of 1 INSERT SELECT with 10000 UNION ALLs. You can still pass the entire batch as a part of a single command.

The advantage of this (despite its various disadvantages-- the use of temporary tables, long command strings, etc) is that it offloads all the string processing to the much more efficient C# side of the equation and doesn't require an additional persistent database object (the Split function; though, again, who doesn't need one of these sometimes)?

If you DO go with a Split() function, I'd encourage you to offload this to a SQLCLR function, and NOT a T-SQL UDF (for the performance reasons illustrated by the link above).

Finally, whatever method you choose, note that you'll have more problems if your data can include strings that contain the delimiter (for instance, In Aaron's answer you run into problems if the data is:

'I pity the foo!', 20120101, 26; 'bar, I say, bar!', 20120612, 32

Again, because C# is better at string handling than T-SQL, you'll be better off without using a T-SQL UDF to handle this.

Edit

Please note the following additional point to think about for the dynamic INSERT option.

You need to decide whether any input here is potentially dangerous input and would need to be cleaned before use. You cannot easily parameterize this data, so this is a significant one. In the place I used this strategy, I already had strong guarantees about the type of the data (in particular, I have used it for seeding a table with a list of integer IDs to process, so I was iterating over integers and not arbitrary, untrusted strings). If you don't have similar assurances, be aware of the dangers of SQL injection.