Create Table in SQL Server using Excel Template

By:   |   Comments (9)   |   Related: 1 | 2 | 3 | > Database Design


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 for 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.


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
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)

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 25, 2021 - 7:08:09 AM - Mohammed Yasin Back To Top (88733)
Thank You very much !
This is helped to save my time for table creations.

Monday, May 7, 2018 - 1:06:17 AM - Francisco Back To Top (75879)

Very good!

Thank you!

Friday, February 10, 2017 - 9:40:56 AM - Surya Back To Top (46162)

Thanks a lot.

Sunday, November 16, 2014 - 8:22:35 AM - Omar Back To Top (35302)


If we need to create a table but we don't knows the number of column to create (Imagine that we create a temporal table with diferents data).

There are some methode to do that.


Tuesday, July 30, 2013 - 5:58:06 AM - Meet Back To Top (26057)

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

This tool made developers' life much easier..

Thanks again..

Wednesday, May 22, 2013 - 3:26:54 AM - Barbi Back To Top (24074)

Thanks this is wonderful helped me a  lot

Wednesday, March 13, 2013 - 12:05:16 PM - Bryan Holmstrom Back To Top (22780)

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, July 7, 2010 - 8:24:31 AM - saineymd Back To Top (5801)

Thank you a thousand times Greg.  This is sublime.

Thursday, May 15, 2008 - 1:02:18 PM - nanao56 Back To Top (999)

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

get free sql tips
agree to terms