The Power of Two
MS-Excel and Notepad Come In Handy in MS-SQL Server Database Migration
Move over Pentaho, Talend.
One interesting projects I got involved is the MS-SQL Server database migration to the new primary database. St. John Ambulance-South Australia (SA) management has decided to merge data with St. John Ambulance-Queensland (QLD) to upgrade its information system and catch up with ‘On-Line’, Net-ready trend. In lieu of arduous and complex ‘back to drawing board’ approach to redesign the database, St. John Ambulance (SA) chose a leap-frog approach by adopting to the schema and database design of QLD database. Obviously, QLD is a net-ready database easily adaptable to on-line systems and web server that is functionally efficient to be viewed, modified and added over the Net.
I did not have any sophisticated ETL software but I know an old trick can do the job. Enter MS-SQL Server and Notepad. I installed MS-SQL-Server on local (my laptop) with QLD database. It was a skeletal database containing only tables that are be involved with data transformation. Here comes Notepad. Notepad is my standard tool in handling data in text file format. It’s a medium for layover text file when data are dumped from the source MS-SQL database (SA) After data are transformed for the target database (QLD), Notepad once again is the place-holder for data in text format prior to upload to MS-SQL Server.
Here comes MS-Excel, This trusty, versatile tool is the staging area where data are manipulated to align with the schema of target database. The contents of a table of source database (SA) lay across the columns of the worksheet with in a way that each field of the table corresponds to a column in the worksheet. The rows of each column (data fields) in the worksheet are the respective records, or contents, of the fields.
A column in the spreadsheet is inserted after each column that contains field contents of source database. The new columns contained macro definitions to convert the field contents of the source table (SA) into the schema of the target database (QLD). The new set of data yielded from the macro functions is converted to a text file. And finally in a breeze , the resulting text file in a Notepad is uploaded to MS-SQL Server in a breeze.
Leonardo da Vinci once said ‘Simplicity is the ultimate sophistication.’ Thus who needs the complexity of MS-SQL Integration Services, or Talend when the simplicity of the power of two – Notepad and Excel can do the job!