I've got a couple of challenges I want help on. I have to pull data directly into my SQL database from arbitrary sources. The particulars are: I understand the precise structure of my database and also the structure won't change. After I do consume new data, it'll occur just once, at that time I setup a clear case of my database. I'll make most all cases of my database and every time it'll have to drag data from the different source, and individuals sources is going to be structured diversely. The information will in all probability contain 1000's of rows of records. The information source will in all probability take place in Stand out, Access, less available Word as well as more rare, it will be in a SQL database. I'm able to think that the majority of the core data would be the same, just place in different locations. They'll consume a general grouping despite how there held. Basically, I am moving data from legacy systems to some SQL system which should be accomplished for many groups plus they need their very own private demonstration of the database. Any ideas how I'd do that? How hard will it be to create a course that will do the majority of this for me personally?
This really is certainly a genuine-world question. Can you really write a course that is going to do the majority of this? Not the majority of this, I believe, but possibly a lot of it.
For every table inside your target system, produce a view that shows the origin data you anticipate to have the ability to place. Choose column names making it simple to tell what needs to be achieved probably you'll choose column names that match the prospective posts inside your
INSERT statement. Save your valuable
INSERT claims as saved methods.
Now, when you're given a brand new supply of data inside a new format, you still need to recreate your sights, but when the sights are exhibiting the best data beneath your selected column names, you are able to run your saved methods without change.
I've got a similar kind of project where information is being retrieved from Access, .ini file, file modification dates, and MySql. I scrape this data every day and essentially append to some set SqlServer schema.
I produced a DataTable so that as I iterate some sites, place the information into each new row. After I possess the DataTable complete, I execute a bulkcopy to append towards the database.
Hopefully can help you out a little. I understand my project does not cover all of the facets of your question but additionally do not have a DBA to supply sights, saved methods, etc. Nor have i got the extra time for you to dedicate to may be. Not probably the most favorable of conditions, but that is the actual way it is.
The easiest way of fixing this issue is by using and ETL (Extract-Transform-Load) solution. The ideal choice is SSIS that is through Microsoft's BI suite.