Transferring data from a database of unknown structure to a databases of known structure

I have a few challenges I need help on. I need to pull data in to my SQL database from arbitrary sources. The details are: I know the exact structure of my database and the structure will not change. When I do take in new data, it will occur only one time, at the time I set up an instance of my database. I will make many instances of my database and each time it will have to pull data from a different source, and those sources will be structured in different ways. The data will most likely contain thousands of rows of records. The data source will most likely be held in Excel, Access, more rare Word and even rarer, it'll be held in a SQL database. I can assume that most of the core data will be the same, just put in different locat开发者_运维百科ions. They will follow a general grouping despite how there held. Essentially, I'm transferring data from legacy systems to a SQL system and this must be done for many groups and they need their own private instance of the database. Any thoughts on how I would do this? How hard would it be to write a program that would do most of this for me?

This is definitely a real-world question. Is it possible to write a program that will do most of this? Not most of this, I think, but perhaps some of it.

For each table in your target system, create a view that displays the source data you expect to be able to insert. Choose column names that make it easy to tell what has to be done; most likely you'll choose column names that match the target columns in your INSERT statement. Save your INSERT statements as stored procedures.

Now, when you are given a new source of data in a new format, you will still have to recreate your views, but once the views are displaying the right data under your chosen column names, you can run your stored procedures without change.

I have a similar type of project where data is being retrieved from Access, .ini file, file modification dates, and MySql. I scrape this data every morning and basically append to a set SqlServer schema.

I created a DataTable and as I iterate a set of directories, insert the data into each new row. Once I have the DataTable complete, I perform a bulkcopy to append to the database.

I hope that helps you out a bit. I know my project doesn't cover all the aspects of your question; but also don't have a DBA to provide views, stored procedures, etc. Nor do I have the additional time to devote to such things. Not the most favorable of conditions, but that's the way it is.

HTH...

The best way of solving this problem is with and ETL (Extract-Transform-Load) solution. A good choice is SSIS which is through Microsoft's BI suite.

This is the building blocks for consciousness or the base......

1 A data base that organizes thousands of files similar to dna,

2 user interface

3 parts are hidden, preventing a system breach/crash