SQL Create Table and Insert Data - A Complete Tutorial

In this tutorial we are going to look at how to create a table and insert data in SQL Server. We also look at how to insert multiple rows into a table.

This is an extensive lesson which covers a number of topics which I have provided the timestamps for below, so feel free to jump to the correct section you are interested in

00:48 - Create a Database
01:18 - How To Create a Table in SQL Server
02:50 - Insert single row into Table
04:20 - Column name or number of supplied values does not match table definition.
04:54 - Insert multiple rows into Table
06:30 - Create Table with Primary Key
07:12 - IDENTITY insert - Auto Incrementing Id Columns
08:00 - NULL VS NOT NULL
09:09 - Considerations when creating SQL tables
10:46 - Cannot insert the value NULL into COLUMN
11:24 - How To Add a Column To Table in SQL
12:43 - Add Primary Key To a table
13:39 - Drop a Primary Key Column in SQL SERVER
14:17 - Drop a column from A Table in SQL

Primary Keys

Primary Keys are required in SQL Server to keep our data unique and to enforce integrity. We want don't want duplicate data and we would like to be able to differentiate two employees
with the same name for example, so we assign each an Id. This makes each row in a SQL Server unique. Understanding how to create a primary key in SQL is vital to good database table design

Auto Incrementing Id Columns

The IDENTITY Keyword is explained in this tutorial. It runs through what it does and why it is needed. When creating Id's we usually want to auto increment our column by 1, however there are times
where we may want to increment the values by 10 or a 100 for example. IDENTITY insert gives us the ability to define that when we create a table.

NULL vs NOT NULL

Do we want a value for every row on insert? We will do for some columns! Here we use the example of a persons name - we would always want their first name, but not always their middle name so NULL and NOT NULL
come into play here. NULL essentially means blank! Do we want this column to be blank or not blank?

Data Types

Creating tables in SQL Server is a relatively easy thing to do, however it is VERY easy to do this incorrectly. A number of things need to be covered, especially data types -
because we need to understand beforehand what data is going into our tables, then we can determine what data types best suit the column.

For example; string data such as Name, Addresses would be suited to having a VARCHAR data type. As this accepts string data. However if we are inserting timestamps VARCHAR wouldn't be
ideal and would look to using a datatype such as DATETIME or simply DATE. SQL will then handle these columns as date and time fields and it makes us easier to query and report on.

The most common types of data types we will see in SQL tables are:

VARCHAR
DATETIME
INT
BIT
FLOAT

Understanding data types is briefly covered in this tutorial but to cover it extensively would be within another video

Errors when Inserting into Tables

This lesson covers a number of errors that we often encounter when inserting both single and multiple rows into SQL Tables. The most common errors are:

Column name or number of supplied values does not match table definition.
There are fewer columns in the insert statement than values specified in the VALUES clause. The number of values in the VALUES column must match number of columns
Cannot insert the value NULL into column, table; column does not allow nulls. insert fails.
The definition for column 'Id' must include a data type

This tutorial shows these error and how to avoid them why they are caused

Resources

How To Create Tables in SQL Server http://www.sqltutorialacademy.com/sql...

Primary Key
https://www.w3schools.com/sql/sql_pri...

Data Types
https://www.w3schools.com/sql/sql_dat...

Microsoft Definition of SQL Tables
https://docs.microsoft.com/en-us/sql/...