SQL从两个相应的逗号分隔的字符串集中插入行

I would like to take two separate strings of value pairs delimited by commas and insert each pair into a row in the database.

我想采用逗号分隔的两个单独的值对字符串,并将每个对插入数据库中的一行。

For example:

X = "1,2,3"     Y = "A,B,C"    =>     X  |  Y   
                                     ---------             
                                      1  |  A
                                      2  |  B
                                      3  |  C

I am using MSSQL 2008, but solutions for any database would be greatly appreciated. Also if there is a better method to handle inserting these sets of data other than just writing a SQL query please explain in detail.

我正在使用MSSQL 2008,但非常感谢任何数据库的解决方案。此外,如果有一个更好的方法来处理插入这些数据集,而不仅仅是编写SQL查询,请详细解释。

2 个解决方案

#1

In SQL Server parse the lists using a method like this: http://www.sommarskog.se/arrays-in-sql-2005.html. Tried and true, works great.

在SQL Server中使用如下方法解析列表:http://www.sommarskog.se/arrays-in-sql-2005.html。尝试和真实,工作得很好。

#2

Sam

INSERT INTO foo (X,Y) VALUES (1,'A'),(2,'B'),(3,'C');

In response to:

回应:

Also if there is a better method to handle inserting these sets of data other than just writing a SQL query please explain in detail.

此外,如果有一个更好的方法来处理插入这些数据集,而不仅仅是编写SQL查询,请详细解释。

There is no way to insert data into a SQL database other than a SQL query.

除了SQL查询之外,无法将数据插入SQL数据库。

#1