MSSQL CREATE TABLE – SQL For Programmers
Once you have your database created, you’ll want to get some tables set up into it. To do this, you’ll need the CREATE TABLE statement that can be issued from the connection object once you’ve established the connection.
The first thing you’ll need to do before executing this command is to establish a connection to your SQL database.
Once you’ve done that, you’ll want to create a SqlCommand object passing in the CREATE TABLE command string and the connection object.
Finally, you’ll open the connection, issue the ExecuteNonQuery() method and close the connection.
The CSharp code for this looks something like:
SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(createTable, conn); conn.Open(); cmd.ExecuteNonQuery(); conn.Close();
Pretty standard code that I hope you are already familiar with.
But what you probably want to know is what to put in the createTable string. Here you go:
string createTable = @"CREATE TABLE tableName ( ColumnName ColumnType(size) [IDENTITY(seed,increment)] [NULL|NOT NULL [DEFAULT defaultData]] [PrimaryKey] [comma delimited column statements go here] )";
I like to use the @ string statement so that I can create my CREATE TABLE statements in SQL Enterprise Manager and copy and paste the results into my code without having to worry about carriage return/linefeed pairs causing me to do extra editing once I paste the code into my CSharp editor.
A column statement is in the form of:
ColumnName ColumnType(size) [NULL|NOT NULL [DEFAULT defaultData]] [Primary Key]
Where ColumnType is one of the various data types available in T-SQL
The datatypes you are most likely to use are:
- varchar(size) or nvarchar(size)
If you want the field to hold NULL values, you can leave the NULL or NOT NULL qualifier out, but if you want to make sure the field always holds some sort of valid data, you’ll want to include the NOT NULL statement after your type and size information.
And if the field is the primary key for the table, include the Primary Key statement at the end of the column description.
If you want the field to auto-increment, you’ll want to include the identity information. Assuming you want to start at 1 and increment by 1, your SQL will look like:
To put this all together in a typical CREATE TABLE string, here is what a typical CREATE TABLE string would look like for a Users table:
string createTable = @"CREATE TABLE [dbo].[Users]( [UserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Username] [nvarchar](100) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [Email] [nvarchar](256) NULL, [DisplayName] [nvarchar](128) NOT NULL DEFAULT (''), )";
There is more to the CREATE TABLE syntax, but this should get most programmers as far as they need to go.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014