SQL Insert rows from two corresponding comma delimited sets of strings

Question

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.

Answer 1

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.

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

Answer 2

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.