inserting efficiently into a temp table/table variable

I am a newbie to Sql Server programming, and I was hoping that someone
might be able to point me in the proper direction.

I am trying to write a C function that takes an array of strings and
insert them into either a temporary table or a table variable. This
table will just be a one column table that contains this list of
strings. (If you want further background, these strings are actually
keys and I will subsequently use this temp table/table variable to do a
join with a much larger table to get a subset of rows based those keys.
I want to do this to avoid using an IN clause.)

How can I efficiently do this? If I have a string array that contains
all the keys, is there a way I could insert all these keys into the
temp table in a bulk fashion? I don't want to do tens or hundreds of
thousands of inserts into a temporary table just to do the join... is
there some other mechanism I could use other than INSERT? I saw a BULK
INSERT but this pertains to bulk loading a file it seems. Is there
some way of doing this totally in memory?

wrote in message

> How can I efficiently do this? If I have a string array that contains
> all the keys, is there a way I could insert all these keys into the
> temp table in a bulk fashion? I don't want to do tens or hundreds of
> thousands of inserts into a temporary table just to do the join... is
> there some other mechanism I could use other than INSERT? I saw a BULK
> INSERT but this pertains to bulk loading a file it seems. Is there
> some way of doing this totally in memory?

If all of your strings concatenated together with a separator character
(like a comma) between them is 8,000 characters or less you can pass the
list as one long VARCHAR comma-separated string (or other separator) to a
stored proc and split them SQL-Server side. Erland Sommarskog has an
article on this: http://www.sommarskog.se/arrays-in-sql.html

If you're using SQL 2K5 you could use the .NET 2.0 SqlClient Bulk Insert
functionality to do this also, although for the work you might not see a
huge performance gain over the above method. Another option, if you want to
do Bulk Insert, is to use the old-school bulk insert API. ODBC, OLE DB, and
even DB-Lib provide access to bulk insert from variables in memory. They
can all be a bit of a hassle to use, however.