Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Simple way to create tables in SQL Server using Excel

MSSQLTips author Greg Robidoux By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Design

Problem
When creating a new application that requires a data model to support it, the best approach is to use a data modeling tool to develop the logical and then physical data model.  Although this is the best approach, not everyone does this for a full blown application let alone for a small utility application or even add on features to an existing database. The development process along with the database design becomes an iterative process where things are developed and then a certain milestone is hit and the database tables need to be modified to accommodate the change.  Again, using a tool to assist with the data modeling is the best approach, but here is a simple approach to generate table structures, use them as they are and then regenerate them when a change is needed.

Solution
Taking the programmatic approach is usually the best way to generate code for either tables, indexes or even stored procedures. This could be done by using VB, C, C#, ASP, etc...  Another way of doing this is by just using a simple Excel spreadsheet.  In the past I have had the need to generate tables for testing new approaches for data loading or even application prototypes.  One simple approach has been to just use Excel, fill out a grid and then generate a table structure based on the parameters that were entered. 

Below is a example creating a table called "dbo.Contact". 

The spreadsheet is setup as follows:

Column Purpose
Table This is used for grouping tables together.  If you only use this to generate one table at a time, this is really not necessary.  But you could use this to have data for several tables.  You could then sort your data to see if you have like columns to make sure the column names are consistent and the datatypes used are the same.
Order This allows for sorting of the data within Excel. This way if you want to sort by the Type, you can do this and then sort again by Order to generate the code.
Column This is the name of the column. As you can see I have other information in some of the columns to specify commands.
Type This is normally used to determine the datatype.  For a list of valid datatypes click here.  As you can see I also used this for other commands such as the table name in rows -3, -2 and 0.
Length This is the length of the column.  This is only needed for the character type fields such as char, varchar, nchar, etc...
Options These are additional options like for setting the column to be an identity value or specifying that the value can be NULL.
Terminator This is the terminator for the end of each line when the command is generated.  Basically this is only needed to separate each column in the table create statement.
Output This is the output of the command.  To run this you would select the data from row -3 to row 1000 and paste into Query Analyzer to generate the table.  This is the only column in the spreadsheet that has a formula and all that it is doing is appending the different values from each column together to create the proper T-SQL statement.

The results from the above are shown below.  This is what gets pasted into Query Analyzer to build the table. 

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.Contact') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Contact
GO
CREATE TABLE dbo.Contact (
ContactID int identity(1,1),
ContactOwnerID int NULL,
FirstName varchar (30) ,
LastName varchar (30) ,
Account varchar (30) NULL,
Title varchar (30) NULL,
Department varchar (30) NULL,
Birthdate datetime NULL,
ReportsTo varchar (30) NULL,
LeadSource varchar (30) NULL,
Phone varchar (15) ,
HomePhone varchar (15) NULL,
MobilePhone varchar (15) NULL,
OtherPhone varchar (15) NULL,
Fax varchar (15) NULL,
Email varchar (30) ,
Assistant varchar (30) NULL,
AsstPhone varchar (15) NULL,
EmailOptOut char (1) NULL,
MailingAddress1 varchar (30) NULL,
MailingAddress2 varchar (30) NULL,
City varchar (30) NULL,
State char (2) NULL,
Zip varchar (10) NULL,
CreatedByID int ,
CreateDate datetime ,
ModifiedByID int ,
ModifiedDate datetime ,
Comments varchar (2000)
)
GO

As you can see this generates code to create a simple table structure.  This is a good way to create your base tables to make sure your application is working as planned during the development stages. If you need to make a change, update the spreadsheet and then copy and paste the code to regenerate the table.  One thing it does not do is preserve any sample or test data, so this is something else you may need to script to make this process easier when you regenerate the tables.

Next Steps

  • Download the sample Excel spreadsheet (xls file)
  • Modify the spreadsheet to be a bit more dynamic, add in indexes, primary keys, etc...
  • Look at ways of creating tools like this using Excel, ASP, VB, etc... to simplify generating code as well as making repetitive tasks easier


Last Update: 8/29/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, May 15, 2008 - 1:02:18 PM - nanao56 Read The Tip

Thank You, Thank You, Thank You.

 I stumbled across your tip and it is such a smart and quick way to create a table!

 Thanks for sharing


Wednesday, July 07, 2010 - 8:24:31 AM - saineymd Read The Tip

Thank you a thousand times Greg.  This is sublime.


Wednesday, March 13, 2013 - 12:05:16 PM - Bryan Holmstrom Read The Tip

Good morning, I love this little tool. Is there a way to add the description to the excel sheet and have it populate the database as well?


Wednesday, May 22, 2013 - 3:26:54 AM - Barbi Read The Tip

Thanks this is wonderful helped me a  lot


Tuesday, July 30, 2013 - 5:58:06 AM - Meet Read The Tip

Wow, Thanks. Tried and could convert this tool for mysql, It worked like charm..

This tool made developers' life much easier..

Thanks again..



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.