将多行数据传递给存储过程 [英] Passing multiple rows of data to a stored procedure
问题描述
我在 C#.net 中有一个对象列表(从多个文本文件创建)，我需要将其存储在 SQL2005 数据库文件中.不幸的是，表值参数始于 SQL2008，因此它们无济于事.我从 MSDN 发现一种方法是将多个数据值捆绑到分隔的字符串或 XML 文档中，然后将这些文本值传递给过程或语句"，但我对存储过程比较陌生，需要更多帮助.我知道我可以创建一个存储过程来创建一个记录，然后遍历我的列表并添加它们，但这就是我想要避免的.谢谢.
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
一些数据库文件字段:
EffectiveDate varchar(50)
MfgName varchar(500)
MfgPartNbr varchar(500)
Cost varchar(200)
QtyOnHand varchar(200)
推荐答案
您可以很容易地从单个字符串拆分多个值.假设您可以像这样捆绑字符串，使用逗号分隔列"，并使用分号分隔行":
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
然后你可以这样查询(为了简单起见，我只处理 3 个属性，但你可以推断出 11 或 n):
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;
结果:
prop1 prop2 prop3
------ -------- -------
foo 20120101 26
bar 20120612 32
这篇关于将多行数据传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
