I have a hashtable (list of key/value pairs) that I want to insert into a database. However, I'd rather not do this by inserting each key/value pair with a separate command since I have a few thousand that need to be inserted and it seems to take too long. Instead, I want to just call one command that will insert the whole list. I found this site: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
that explains a few ways to do this. The method that uses a temporary table variable seems like it would be best for me but I'm not sure. Basically, I have three questions:
1) Will I actually get a sizable performance benefit from just making one call to a stored procedure instead of thousands (will the decreased command overhead outweigh the string processing and stuff)? I think the answer is a definite yes but I'm not an SQL expert.
2) Can you please provide a stored procedure that will let me pass it a comma-separated list of keys, another comma-separated list of values, and a foreign key and have them inserted in the database. Like this:
Pass the stored proc this:
And inserts this:
Table'sPrimaryKey ForeignKey Key1 Value1
Table'sPrimaryKey ForeignKey Key2 Value2
Table'sPrimaryKey ForeignKey Key3 Value3
3) Can you please provide a stored procedure that will return a comma-separated list of keys and values for a given ForeignKey.