So you've got an overzealous admin on your SQL server who give you permission to do anything - create a function, run a procedure - harsh.
You can probably live without those things as you can always do things the long way, but when you've no privaledges to create tables when you need to query a long list of external data it can be infuriating.
There are a couple of ways around it but if you are talking about a long list of things then you soon run into trouble. My original goto methods involved basically constructing the list in the SQL editor and sending it over to the server. These have their limitations:
My first solution is to hardcode it into the statement:
SELECT * FROM dataset WHERE cust_id IN (1,2,3,4,5,6,7,8,9,10,.........,9999998,9999999,100000000
however, there's a physical limit of how much code you can actually send to the server at one time.
If you reach this limit you have to use this technique of creating a temp table and writing the valules into it,:
CREATE TABLE #tmp_list ( cust_id int ) INSERT INTO tmp_list (cust_id ) VALUES ( 1 ) INSERT INTO tmp_list (cust_id ) VALUES ( 2 ) INSERT INTO tmp_list (cust_id ) VALUES ( 3 ) INSERT INTO tmp_list (cust_id ) VALUES ( 999998 ) INSERT INTO tmp_list (cust_id ) VALUES ( 999999 ) INSERT INTO tmp_list (cust_id ) VALUES ( 1000000 )
the weakness here is that you can only write 10,000 records at a time and if you have too many columns to write you are going to hit the problem above and will reach the limit of the data you can transmit at once. Fortunately you can break it up into 10,000 line chunks but this can get annoying quickly.
So I've been working on it a while now and have finally cracked it. I've been perserverring with the import wizrad (which frustrates me on a regualr basis on how