Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Another way of looping and inserting into database

Posted on 2012-09-10
15
Medium Priority
?
592 Views
Last Modified: 2012-09-17
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?

 foreach (var item in retVal.assetArray)
                    {
                       // how to compare item.Id to database's table and insert or update?

                    } 

Open in new window

0
Comment
Question by:Camillia
13 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38383651
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
0
 
LVL 7

Author Comment

by:Camillia
ID: 38384020
it's sql 2005. What to do?
0
 
LVL 20

Expert Comment

by:informaniac
ID: 38384328
If it was a just insert SQLBulkCopy could have helped, but here I only see option 1 as the available option.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 7

Author Comment

by:Camillia
ID: 38385085
not sure why my coworker mentioned #2. He said to use an array but i dont think that's a good option.
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 500 total points
ID: 38385276
I wouldn't agree with either option. Both will probably be excruciatingly slow. I'd recomment pushing the data up into a holding table and then running a stored procedure to do the update of the end table.

1. Export the collection to a .CSV file
2. Run a stored proc to Bulk Insert the csv file into a holding table (see here for a good description of how to do it)
3. Run a second sproc (probably from the first) to do the update of your current table from the staging table.
4. At the end of the sproc, delete the holding table.

This way you get the database to do what it's good at (working with large chunks of data) and the ASP to do what it's designed for (user interaction).

hth

Mike
0
 
LVL 7

Author Comment

by:Camillia
ID: 38385291
Mike, let me look at your solution. Thanks
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38385435
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
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 500 total points
ID: 38385635
Yes, staging could be an option. Not sure how much is the total size of your table and if load replace will work (e.g. are all columns for the existing row to be updated or only select few)

Other option would be to use LINQ with your collection as one table and the Table as another source for your query.
0
 
LVL 7

Author Comment

by:Camillia
ID: 38386734
let me read and i will post back
0
 
LVL 7

Author Comment

by:Camillia
ID: 38387071
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
0
 
LVL 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 500 total points
ID: 38394208
One thing you could do - not necessarily very efficient but it should work and simple to code.

Loop through your current collection of data and run an INSERT inside a try...catch block with the ID but with dummy data.  If the record exists - you catch the exception and continue inserting, if it doesn't exist then it will be inserted.
Then you loop through again and perform an UPDATE (and you know ALL records do exist).
0
 
LVL 7

Author Comment

by:Camillia
ID: 38394579
thanks, let me read
0
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 38395824
Is there a unique key on these records?  If so, create a unique index on the column and perform two queries between the current production table and an uploaded temp/import table.  The first query performs an update of the joined tables.  The second does an append (insert) of the records.  Since there is a unique index, only new rows will be added to your table.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question