Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_mad
Starting Member
5 Posts |
 Posted - 2007-07-25 : 09:22:10
|
| I need to create a script which iterates over a list of about 40,000 id's. As the list goes through each id other stored procedures are called which do something. Because of access rights on the server im running the script i need to hard code the 40,000 records in my script. The id's are currently in an excel fileWhat way can i store them in the script, should i use a cursor, and if so how can i create a cursor to hold so many hard coded valuesAny help or alternative ideas would be much appreciated |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-25 : 10:37:09
|
| Why not load from the excel file into a table?Otherwise I would generate a load of insert statements from the excel spreadsheet and copy them into the SP inserting into a temp table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sql_mad
Starting Member
5 Posts |
Posted - 2007-07-25 : 10:49:15
|
| thanks for your suggestions but the server im accessing is restricted and i cant save the excel file there, also i dont have write access on the db so i cant create a table to read the id's into.im looking at an alternative in the form of a variable of type tablehowever im not sure if i can hardcode values into this type e.g. Declare @tbl table (clnt_id_n int)Insert @tbl (759,8761,8782,8786,8797,8801,8820,8821) |
|
|
|
sql_mad
Starting Member
5 Posts |
Posted - 2007-07-25 : 11:01:38
|
| im now using the variable of type array however im wondering is there a way i can do an insert in one statement instead of having to do a seperate insert for each value:Declare @tbl table (clnt_id_n int)insert into @tbl (clnt_id_n) values (8688)insert into @tbl (clnt_id_n) values (8699)insert into @tbl (clnt_id_n) values (8702)i will need over 40,000 insert statments this way, is there a short cut? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-25 : 11:44:48
|
quote:
Originally posted by sql_mad
i will need over 40,000 insert statments this way, is there a short cut?
Not muchINSERT INTO myTableSELECT number FROM myNumbersTableOrFunctionWHERE number IN(... csv list of numbers goes here...) You could get more fancy if your numbers are largely consecutive.EDIT - forgot a bracket |
|
|
|
sshelper
Posting Yak Master
216 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-07-25 : 11:59:53
|
| Another possibility would be to pass an array or CSV list and use a Split function to parse them into a temp table (or table variable) that you could join to.-Ryan |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
hardcoding a list of id's in a stored procedure