importing data - advice


I will have a .net application that stores data for payments

There are 3 + sites that all run the same desktop app

Every day, and backup will need to be done onto USB drive.

All 3+ sites hand their USB drive to the boss, and he then will need to import all data onto his computer running the same application

this computer will generate reports on the data


however, i'm really unsure how to do the backup/import
the data structure is the same, and they are all running the same software, but will be collecting different data

all this data will need to be merged every day manually,

I dont want the user to interact with SQLserver directly..... the .net application will have to do this

can someone suggest an approach of how i export and import the data with getting data integrity issues?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Here's what I would do:
1. first make sure data can be identified where it comes from - which site - so have an siteid column in the export
2. export data to USB or something else in excel or CSV format
3. create a SSIS package or SQL stored procedure that you can call from .NET and can import the CSV or EXCEL into SQL database staging table.
4. validate data from the staging table and populate your permanent DB table.
Kevin CrossChief Technology OfficerCommented:
Do you not have a central SQL Server? Or is there an application requirement for individual SQL instances per client? In other words, if you can have your application write to the same repository, the need for export / import and the USB drive becomes unnecessary. That is really a security risk anyway if we talking about sensitive payment information. Then you through in data integrity and might just be better to avoid the whole situation.

Anyway, if this MUST be done and you do not want interaction with SQL, you can have your code do the export to some common format like say XML or Excel and then have an import function in the application that takes this same format. You can always have a DataSet in code that merges all the inputs of imports allowing the manager to do conflict resolution between them before sending to backend database.

Hope that helps!
websssAuthor Commented:
Thanks guys
Payments are in cash, its a microfinance project in a slum

Each computer is not connected to network or internet

Hope that clears it up?
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Kevin CrossChief Technology OfficerCommented:
Okay. XML files option is still viable. The other option if security is still s concern even though you are dealing in cash is to use .net to write to SQL server compact .sdf file that is password protected/encrypted that you then attach in your manager's .net application.
websssAuthor Commented:

Sorry, I need to clarify something else
All 3 slums computers and the managers need to run the same .net application

They will all have an import/export facility, but the difference being only the manager will use it on a frequent basis

So basically it's One .net app that can handle importing and exporting different data

Does this change things now?
Kevin CrossChief Technology OfficerCommented:
No. I would look into the same techniques I mentioned.
websssAuthor Commented:
Ok thanks

So on the import, would I drop all records for that particular siteid, then import all the csv/XML ones?

I'm not sure how I Handle duplicates that's all
Kevin CrossChief Technology OfficerCommented:
You can merge the data (i.e., INSERT new rows and UPDATE existing ones), depending on your business needs. When doing the INSERT, you can filter records that already exist in the destination table:

INSERT INTO destination( <column_list> )
SELECT <column_list>
FROM source
   FROM destination
   WHERE destination.key_column = source.key_column


Where key_column represents one or more columns whose values uniquely identify duplicate rows.
websssAuthor Commented:
Thanks mwvisa1

However what about records that have been updated?
wouldn't this ignore any updates?
Kevin CrossChief Technology OfficerCommented:
Yes, that was just an example of he insert for new records. As I said above, you would do an update for existing records.

SET tto.<column> = tfr.<column>
FROM destination AS tto
JOIN source AS tfr ON tfr.key_column = tto.key_column

With SQL 2008, you can take advantage of MERGE to do both at once.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.