Compare records in SQL database with an Excel, and update database accordingly

Question

I have a table with about 100 columns and about 10000 rows. Periodically, I will receive an Excel with similar data and I now need to update the table. If new rows exist in Excel, I have to add them to the db. If old rows have been updated, I need to update the rows in the db. If some rows have been deleted, I need to delete the row from my main table and add to another table.

I have thought about proceeding as follows:

Fetch all rows from db into a DataSet. Import all rows from Excel into a DataSet. Compare these 2 DataSets now using joins and perform the required operations.

I have never worked with data of this magnitude and am worried about the performance. Let me know the ideal way to go about realizing this requirement.

Thanks in advance. :)

have you thought about just doing it all in SQL? i.e. Import the excel doc, then UPDATE your table where the ID field matches, etc. Much easier than coding — MikeSmithDev
– MikeSmithDev, Commented Nov 9, 2012 at 20:35
You say you want to delete all records not in the Excel sheet, update all records that are changed in the Excel sheet, and insert all records that are new in the Excel sheet. If you want your table to look identical to the Excel sheet at the end of the day, why not drop all the old data and repopulate the table with the new stuff? — user1726343
– user1726343, Commented Nov 9, 2012 at 20:35
@MikeSmithDev If his table has data not in the Excel sheet it will be deleted, as he has stated himself. — user1726343
– user1726343, Commented Nov 9, 2012 at 20:38
oh i see. but still he needs to move those rows to another table... so need to ID those rows. — MikeSmithDev
– MikeSmithDev, Commented Nov 9, 2012 at 20:40

Z . · Accepted Answer · 2012-11-09 20:36:52Z

3

don't worry about the performance with 10k records, you will not notice it...

maybe a better way to do it is to import the excel file in a temp table and do the processing with a couple simple sql queries... you'll save on dev time and it will potentially perform better...

answered Nov 9, 2012 at 20:36

Z .

12.8k2 gold badges34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fahad Over a year ago

i also have about a 100 columns so a little worried about the performance. Haven't ever worked with data of this magnitude so a little anxious. :) Will try creating a temp table as suggested for the Excel data and then merge the 2 :) Thanks :)

Rikki Over a year ago

-1 Performance depends on many stuffs. As the questioner already said, they worry about the performance and it they might have many situations like heavy traffic, poor hardware, network problems and many other possible things that we don't know. So the performance is a problem, even if it's one record.

Rikki · Accepted Answer · 2012-11-09 20:47:12Z

2

As my experience says, its so simple if you choose to do the stuff in t-sql as following:

You can use "OPENROWSET", "OPENQUERY", linked servers, DTS and many other thing in SQL Server to import the excel file into a temporary table.
You can write some simple queries to do that. If you are using SQL 2008, "MERGE" has exacly made for your question.

Another thing is that the performance is far different than C#. You can use "TOP" clause to chunk the comparison and do many other things.

Hope it helps. Cheers

edited Nov 9, 2012 at 20:47

answered Nov 9, 2012 at 20:38

Rikki

3,5681 gold badge25 silver badges34 bronze badges

3 Comments

Fahad Over a year ago

I want to give the user an interface where all these operations will be done automatically, so I do not think the first answer will work for me. I will read up on your first suggestion anyways, since I do not know about anything other than dtswizard. Any pointers where to start would be helpful. Did not know about the MERGE statement, I think it will suit my purpose perfectly. Thanks a ton :) Again, I do not know what you mean by TOP clause, please bear with me, a beginner here. I will google it out, but any pointers on where to read up on it will be very helpful. :)

Rikki Over a year ago

If you need an interface, it doesn't change the way. If I were you, I'd make a database procedure to do the operation, then write an interface to give user some abilities to control, cancel, and see the results of the operation. By the way TOP clause limits the records you wanna work with (inserting, updating, merging or anything). You should first learn about SELECT clause, everything starts from there... ;) Cheers bro.

Rikki Over a year ago

Another thing that, if I was there, I'd like to have a bear with you. I've got stuck in this hell... :)

Collectives™ on Stack Overflow

Compare records in SQL database with an Excel, and update database accordingly

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related