Executive Summary
This
project is a database table editor that is geared toward those who use
databases heavily, and in particular those who frequently insert, update, or
delete from tables. The business purpose grew from needs that I had as an
Implementation Consultant for government Enterprise Resource Planning (ERP)
systems. A large part of my job was to configure database tables that served as
references for how the rest of the system operated. As more groups within the
government agencies were brought into the system and the level of maintenance
increased, we frequently copied existing configuration and made modifications
as necessary to accommodate the new groups. The modifications were frequently
done in Excel due to its flexibility in writing formulas to generate the
changes that were needed.
Our system
database for several years has been SQL Server 2008, and we frequently copied
and pasted directly from Excel into SQL Server. I began to run into limits with
this method when dealing with several thousand inserts, where copying nearly
13,000 rows took approximately 15 minutes to accomplish. In order to deal with
this I would write Excel formulas to generate insert scripts which could be run
in SQL Server Management Studio in less than 20 seconds. While this was a huge
increase in performance, the downside was the need for me to write a new formula
for generating scripts for every table. Based on the bottlenecks described,
this project is meant to simplify two processes:
- Copying data from Excel to the database.
- Generating insert scripts for running directly in a database GUI.
http://files.gove.net/shares/files/13w/buckylar/Final_Project.xlsm
No comments:
Post a Comment