How To Create A Table Sql Server

What is a Table?

A Table is an object which stores data in Row & Column format. Below Diagram, shows Rows and Column respectively.

Table Rows and Column

In this tutorial, you will learn:

• What is a Table?
• How to Create a Table in SQL Server with T-SQL
• Using T-SQL
• T-SQL: Create a New Table using an existing table.
• Using Table Designer
• Alter Table in SQL Server
• Using T-SQL
• Using Table designer
• Delete Table in SQL Server
• Using SQL Server Management Studio
• Using T-SQL

How to Create a Table in SQL Server with T-SQL

The first step to store data in the database is to create a Table where data will reside. Post creation of the table, we can keep inserting the rows in the table.

We can Create a table in the following ways:

1. T-SQL: Create a New Table by defining all columns and its data type.
2. T-SQL: Create New Table using an existing table
3. Using Table Designer

Let's study this in detail:

T-SQL: Create a New Table by defining all columns and its data type.

Below is the Syntax to create table in SQL Server

Syntax:

CREATE TABLE tableName (    column_1 datatype [ NULL | NOT NULL ],   column_2 datatype [ NULL | NOT NULL ],   ... );

Here,

• The parameter tableName denotes the name of the table that you are going to create.
• The parameters column_1, column_2… denote the columns to be added to the table.
• A column should be specified as either NULL or NOT NULL. If you don't specify, SQL Server will take NULL as the default.

Let's create a basic Table with two columns

Query:

CREATE TABLE COURSE ( Course_ID Int, Course_Name Varchar(10) )

Pre-Requisite: Select the Database where you need to create the table.

Step 1) Run the query by clicking on Execute.
Before running the query, no table exists:

Result: After running the query, click 'Refresh' Button the new table exists as a Course.

Step 2) Insert some data

Till now, the Table only exists, but there is no data in the table created yet.

Transact-SQL has the INSERT statement that can be used to insert data into a table. With this statement, we can insert either one or more columns into a table.

Syntax

INSERT INTO tableName (column_1, column_2, ... ) VALUES (expression_1, expression_2, ... ), (expression_1, expression_2, ... ), ...;

The above shows the basic syntax of the command when using the VALUES keyword to insert data into a table.

Let's insert four rows using Insert command.

Insert into COURSE values (1,'SQL'); Insert into COURSE values (2,'Python'); Insert into COURSE values (3,'SQL'); Insert into COURSE values (4,'C');

Below snapshot shows that now insertion of four rows is successful.

Step 3) Use the SELECT statement to view Data
To view data in SQL Server, Copy following syntax:

SELECT expression FROM tableName [WHERE condition];

Example Query:

SELECT * FROM COURSE;

Result: Table got created, and there are four records in tables. Note that we can create records with a duplicate value in any of the columns as there are no constraints.

T-SQL: Create a New Table using an existing table.

Now say we want another table like COURSE table created above. However, we need only one column of Course_Name and not Course_ID.

Syntax:

SELECT (Column 1, …) INTO <New Table name> FROM <Old Table name>;

Example Query:

SELECT COURSE_NAME INTO COURSE_NAMES FROM COURSE;

Step 1) Run the query by clicking on Execute.

Result: New table created with the name as COURSE_NAMES with existing 1 column and four records from Older Table.

SELECT * FROM COURSE_NAMES;

Using Table Designer

We can also create Table from Studio Management IDE using Table Designer.

Step 1) Right Click on Table>New>Click on Table.

Step 2) Enter column names, data types. Select whether to allow nulls or not.

Step 3) Press 'CTRL+S' to Save the table. Enter Name as 'Course_Title'

Result: Click on 'Refresh' and we can seeNew table exists with the name as Course_Title with one column named as Course_Name.

Alter Table in SQL Server

There are two ways to Alter Table in SQL server.

1. T-SQL: Alter Table by adding new columns.

2. Using Table designer

T-SQL: Alter Table by inserting columns

Below is the syntax of Alter command in SQL:

Syntax:

            Alter TABLE <Table name> ADD Column1 datatype, Column2 datatype;

Query:

ALTER TABLE dbo.Course_Title ADD Course_Duration VARCHAR(20);

Step 1) Run the Query by Clicking on Execute.

Result: New column exists with the name as Course_Duration within the Course_Title Table.

Using Table designer

We can also Alter Table from UI using Table Designer.

Step 1) Right Click on the existing Table>Design.

Step 2) Add Column Name as Course_Start_Date and select Datatype.

Step 3) Press 'CTRL+S' to Save the newly added column.

Result: New Column is created with the name as Course_Start_Date in Course_Title table.

Delete Table in SQL Server

We delete the table when it is not required anymore.

There are two ways to Delete Table in SQL server.

1. Using SQL Server Management Studio.

2. T-SQL: Delete Table.

Using SQL Server Management Studio

Step 1) Right Click on existing Table>Delete. Table Name 'Course_Title'

Step 2) On 'Delete Object' window, Click OK.

Result: 'Course_Title' table got deleted from the Table list.

T-SQL: Delete Table

Below is the Syntax to Drop table

Syntax:

DROP TABLE <tableName>;

The parameter tableName is the name of the table that is to be deleted.

Query:

DROP TABLE COURSE_NAMES;

Step 1) Run the Query by Clicking on Execute.

Result: 'Course_Name' table got deleted from the Table list.

Alternatively, you can also use the DELETE TABLE command. But, it will delete only rows (data). Table structure will remain intact. The command is intended to truncate the table so it can be used later.

Syntax:

DELETE TABLE <Table name>;

Interesting Facts!

• We can also store big files like .xml in a column as BLOB, CLOB datatype.
• Delete can roll back, but Drop cannot be rollback.

Summary:

• Delete table only deletes all the rows, but the table structure still exists. We can again insert new rows.
• The drop will permanently delete the Table structure, and hence we cannot insert new rows.

How To Create A Table Sql Server

Source: https://www.guru99.com/sql-server-table-create-alter-drop.html

Posted by: silvawint1952.blogspot.com

Share this post