Solved

VB.net Excel Add-in Fastest way to validate data against a SQL database

Posted on 2014-09-24
3
460 Views
Last Modified: 2014-09-25
Hi

I have an Excel add-in (VB.net) that is used to upload data to a SQL table.
The process requires a number of checks against other SQL tables for eg machine part number
before it is uploaded. This slows things down a lot.
Is there a way to get the data to the server first (without creating additional tables) to speed
up things?
Or would it be better to pull a datatable down from SQL?
What code would I use?
Thanks
0
Comment
Question by:murbro
3 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40343660
Maybe you need to rethink about that process.
Why it's Excel doing all the work? Why not pass the hard work to SQL Server?
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40343788
>Is there a way to get the data to the server first (without creating additional tables) to speed
 up things?
A vastly better idea would be to first load the data into SQL Server using a 'staging' table, or more than one table if needed, without validations.   I usually prefix these tables ssis_ or stg_.

Then you can write and execute Stored Procedures code to perform any data validations / JOINs / whatever in the staging table, before inserting into the ultimate destination table.
0
 

Author Closing Comment

by:murbro
ID: 40344643
Thanks Jim
Great answer
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 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