How to Create Tables in SQL Server: A Beginner's Guide

The CREATE TABLE statement in SQL Server is fundamental for database design, allowing you to structure and organize your data effectively. This guide will walk you through the essentials of using the CREATE TABLE statement to build new tables within your SQL Server database.

Basic Syntax of CREATE TABLE

The foundation of creating a table lies in understanding its syntax. Here’s the basic structure:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

In this syntax:

CREATE TABLE is the command that initiates the table creation process.
table_name is where you specify the name you wish to give to your new table. Choose a descriptive and relevant name.
Parentheses () enclose the column definitions.
Each column definition consists of:
- column_name: The name of the column.
- datatype: Specifies the kind of data the column will store (e.g., text, numbers, dates). For a comprehensive list, refer to the SQL Data Types Reference.

Let’s illustrate with an example. Suppose you want to create a table named “Persons” to store personal information.

Example: Creating a Persons Table

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

In this example:

We create a table named “Persons”.
It includes five columns: PersonID, LastName, FirstName, Address, and City.
PersonID is defined as int, meaning it will store integer values (like unique identifiers).
LastName, FirstName, Address, and City are defined as varchar(255), which means they will store variable-length strings (text) up to a maximum of 255 characters.

After executing this CREATE TABLE statement, an empty table named “Persons” is created in your database, structured as shown below.

PersonID	LastName	FirstName	Address	City

This empty “Persons” table is now ready to be populated with data using the SQL INSERT INTO statement, allowing you to add records of individual persons.

Creating a Table from an Existing Table

SQL Server also allows you to create a new table based on the structure and potentially the data of an existing table. This is useful for creating backups or subsets of data.

Syntax for Creating Table from Existing Table

CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE condition;

Here:

CREATE TABLE new_table_name AS SELECT ... FROM existing_table_name is the structure for copying table definitions and data.
SELECT column1, column2,... lets you specify which columns from the existing table you want to include in the new table. To copy all columns, you can use SELECT *.
FROM existing_table_name indicates the source table.
WHERE condition is optional and allows you to filter which rows from the original table are copied to the new table.

Example: Copying Data to a New Table

Let’s say you have a “Customers” table and you want to create a new table “TestCustomers” with only customer names and contact information.

CREATE TABLE TestCustomers AS
SELECT customername, contactname
FROM customers;

This statement creates “TestCustomers” table, copying the customername and contactname columns and their data from the “customers” table.

Conclusion

The CREATE TABLE statement is a powerful tool for defining the structure of your database in SQL Server. Whether you are starting from scratch or replicating existing table structures, understanding how to use CREATE TABLE is essential for effective database management and data organization. From here, you can proceed to populate your tables with data and further refine your database schema as needed.

❮ Previous Next ❯