Published on

October 20, 2009

Automating SQL Server Database Creation

Picture the scenario. You have a brand new database to build, for a substantial application that has been designed and tested by a third-party software house. Your job is to create this database in time for the application to be deployed. That is the contract. “Well, that’s easy enough, simply get the database build scripts from the supplier, and run them! Where’s the problem?”

In an ideal (or even mostly reasonable) world, there would be no problem. The problem is that the supplier is not playing ball. The supplier rep is saying that his people are not willing to ‘publish’ any scripts that they would then be obliged to test, support, and maintain, and what is more, lack the creativity to suggest any reasonable, workable alternatives.

So, what do you do in this situation? In this blog post, we will explore a solution to this problem by automating the SQL Server database creation process.

Step 1: Analyzing the Database Specification

The supplier has provided a specification in the form of an Excel spreadsheet, listing all the field information (name, size, type) for over 300 tables and 7000+ fields. The spreadsheet also includes additional information such as extract ID, version, source type, nullable, and key.

After manually entering the first few tables, it becomes clear that there must be an easier way to create the database. Manually creating 4 tables took a significant amount of time, and with over 300 tables to create, it would be a laborious task.

Step 2: Automating the Database Creation Process

To automate the database creation process, we can leverage the power of Excel and SQL Server. By using Excel formulas and functions, we can transform the spreadsheet data into SQL statements that can be executed in SQL Server Management Studio.

First, we arrange the columns in Excel to include only the relevant information. Then, we filter the next table from the specification and copy the block of data into a separate Excel spreadsheet. We repeat this process for each table.

Next, we switch to SQL Server Management Studio and open a new query. We paste the create table clause from the Excel spreadsheet, execute the query, and save the script. We then switch back to the Excel spreadsheet and select the next table.

By following this simple process, we can significantly reduce the time required to create the database. What initially took an hour to create 4 tables manually, now takes only a minute.

Step 3: Taking Automation to the Next Level

While the Excel-based solution is effective, it still requires manual copying and pasting between Excel and SQL Server Management Studio. To further automate the process, we can import the Excel specification into a SQL Server table and process it directly in SQL Server.

By reading the specification line by line, we can dynamically create the tables in SQL Server based on the provided information. This eliminates the need for manual copying and pasting, saving even more time and effort.

With this automated solution, we can complete the creation of over 300 tables in approximately 10 hours, saving around 70 man-hours of work.

Conclusion

Automating the SQL Server database creation process can be a lifesaver when dealing with uncooperative suppliers or complex database specifications. By leveraging the power of Excel and SQL Server, we can significantly reduce the time and effort required to create a database.

While this solution may not be needed for every project, it remains a useful tool to have in your arsenal. It can be especially valuable when dealing with updated specifications or uncooperative suppliers.

Remember, in the world of business, it’s always good to be prepared and have a few tricks up your sleeve. Automating the database creation process is just one example of how you can save time and frustration in your SQL Server projects.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.