Bulk Insertion into database from asp.net

[montu3377]

Hi, There is a requirement to insert data into Bulk like 20,000 - 30,000 rows into database. Description: I have to get one number from text file,bifercate that number and from 1st 5 digit i have to find out ID from sql server 2005 table and then go for second number of that number ,find out ID from sql server 2005 table,this process happens for 4 times for that one number and i have to do same process for 20000 rows. Once i get all those IDs into variables into vb.net from sql server 2005,i have to insert those into one physical table and than one package will run that insert those records into another table. Here main problem i am feeling is speed issue,to process 20000 rows for getting ids and inserting back into sql server takes somuch time. So can some body help me out which way to go to insert records into database? thanks montu3377

[Kschuler]

Could you clarify the issue of getting the number from the text file and looking up other numbers from it? Do all of the four numbers look up an id from the same table or different tables? It sounds like it might be possible to do this with a clever SQL statement, but without more information I can't help you. Another thing, it sounds like after you get all of your data you insert all 20,000-30,000 rows individually. I think it would be faster to input all of the data into a DataTable and use a DataAdapter to update all of the rows in one step.

[montu3377]

Sorry for late reply... Let me clarify all more clear. Could you clarify the issue of getting the number from the text file and looking up other numbers from it? Ans: Actually i am having one text file,now there are one column which stores information like a unique number : example: ABC1234A12B243C334 Now from this type of number i have to slip this number like ABC1234 and find that number in one table in db get the according id of that which return 1222 for that row,store this value some where,Now go to another number of same full number that is A12 and find the id from table (yeh definatly all the tables are different not from same table) then store this value somewhere and go on for this number "B243" and find Id from another table and store value somewhere do same thing upto end of the same number (till C334). Tables are different not same. I have to do the same thing for 20,000-30000 rows of that text file. If i do one by one than it takes lots of time becoz in that case i have to take like 20,000-30,000 connection on that time appication may hang up. Once i get all the values(i.e. all the ids) need to insert into one table. Can you pls. tell me that how to do bulk insertion?? montu3377