Another way of looping and inserting into database
I have 100,000 plus rows in a table with an Id field.
I have a loop in C# code. I need to loop thru the collection (it has an ID column) and if collection-id does not exsit in that table, i want to insert
in the table. If the collection-id exists, I want to update the row in the database.
I thought of doing this:
1. Have the loop. In the loop, call a stored proc. In the stored proc, see if the Id exists/doesnt exist. Update/Insert database accordingly.
2. Load the 100,000 plus rows from the table in a datatable in the code. Loop thru the datatable. If exists/doesnt exist, call database and update/insert accordingly
What should i do?
I have a loop in C# code. I need to loop thru the collection (it has an ID column) and if collection-id does not exsit in that table, i want to insert
in the table. If the collection-id exists, I want to update the row in the database.
I thought of doing this:
1. Have the loop. In the loop, call a stored proc. In the stored proc, see if the Id exists/doesnt exist. Update/Insert database accordingly.
2. Load the 100,000 plus rows from the table in a datatable in the code. Loop thru the datatable. If exists/doesnt exist, call database and update/insert accordingly
What should i do?
foreach (var item in retVal.assetArray)
{
// how to compare item.Id to database's table and insert or update?
} 
Éric Moreau
is your database sql 2008 or better? in this case, you can use Merge: http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
ASKER
it's sql 2005. What to do?
If it was a just insert SQLBulkCopy could have helped, but here I only see option 1 as the available option.
ASKER
not sure why my coworker mentioned #2. He said to use an array but i dont think that's a good option.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mike, let me look at your solution. Thanks
Pinal Dave's blog post should get you to the point of being able to import a csv file into SQL Server.
I'll help you write the code, but will need far more info - file layouts and conditions
Mike
I'll help you write the code, but will need far more info - file layouts and conditions
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me read and i will post back
ASKER
this is what my manager says to do:
pass an array of the IDs from the code to the stored proc and do the update/insert in the stored proc...so..in code...i first loop thru the collection, add them to an array, pass array to stored proc, loop thru that array in SP and then update/insert based on each row
pass an array of the IDs from the code to the stored proc and do the update/insert in the stored proc...so..in code...i first loop thru the collection, add them to an array, pass array to stored proc, loop thru that array in SP and then update/insert based on each row
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, let me read
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.