• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

importing data - advice

Hi

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?

Thanks
0
websss
Asked:
websss
  • 5
  • 4
1 Solution
 
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.
0
 
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!
0
 
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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.
0
 
websssAuthor Commented:
Thanks

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?
0
 
Kevin CrossChief Technology OfficerCommented:
No. I would look into the same techniques I mentioned.
0
 
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
0
 
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
WHERE NOT EXISTS (
   SELECT 1
   FROM destination
   WHERE destination.key_column = source.key_column
)

;

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

However what about records that have been updated?
wouldn't this ignore any updates?
0
 
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.

UPDATE tto
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now