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

Primary Key

Data Types

Microsoft Definition of SQL Tables

[GodzGem]

Man, the most clear and concise instructional video I’ve found. You did a great job with the walk through and really walked us through it. Didn’t have skip a second… I LOVE SQL !!!, I’m happy you’re showing love back to the tech community.

[anthonynorman]

I often find my self coming back to your channel.
Videos are very simple and easy to understand.

I am not a full time dev, been trying to get a promotion by developing on the side of my regular work and using the results to speed things up.

Thank for the videos and hope you get many more subscribers.

[DublinObserver]

In my opinion, this is the most clear, brief but very explanatory video, just straight to the point. Thanks so much, I managed to complete my assignment with your help.

[schumacher]

Very helpful, clear and to the point! Many thanks

[roywit]

Perfect tutorial just subbed

I've noticed many SSMS Videos do not describe about the primary key, this has helped thanks

[PostHeartBreak]

You just gained a subscriber, can’t wait to explore more of your videos..keep up the good work

[voidthinker]

Very nice. Brief and precise videos. It helped me a lot in understanding of table creation with SQL queries.

[arturcarreira]

Please, could you help me with a question?

What is the diference between
Create type [...] as table
and
create table ()

?
Thanks

[teamvamospickleball]

when inserting percentages into a table, do you set the column up as int?

[oratilegodwill]

best ever ... finally someone who knows what he's doing...!!!

[samreenfatima]

Thank you so much, I am new to SQL have been trying to create table but it was giving error 208, went through a lot of videos but nothing helped.This is the only video that helped me create my first table. Thank you so much SQL Guy 😊😊

[thanunvsn]

Thank you for the video, your instruction is way better than my professor explanation.

[Fabian-ewly]

Why did you add the [Id] column using ALTER TABLE, when you had already defined [Id] as a column in the CREATE TABLE syntax above it?

[skurakox]

Finally, understood what is going on with creating tables

[MrAmrshebl]

Loved the video. I have a question please : I have a table that has two columns;Id & tag names and another table that has much more info but they have the tagnames in it. I want to query every single element in table 1 to find if it exists in table 2. I want this query to run every 5 minutes. How can I do that?
thanks in advance

[rosannaasejo]

very nice explination. hoping you will upload more video

[chalkpitbullwatermelon]

does anyone know why when I create the table, it doesn't show up under the tables option in the left. I switched the top left thing to the name of my database I was using but it still doesn't show up and I'm not sure how to reopen the query I made under it.

[Steven-mfdb]

I notice you use the abbreviation "tbl" for table. I wrote this in a draft design spec recently and my new boss ("who knew the job inside out") had never heard of such a thing and made me change it to TABLE telling the rest of the team that it wasn't a thing. I feel a sense of vindication that The SQL Guy uses it too.

[HIghlordBalkan]

All I get when running something similar is "Incorrect syntax near 'FirstName' _" and no new table to be found. Following exactly what I'm seeing in the video so I'm unsure as to what I'm doing wrong.

[jorgedelacruz]

Hi nice work, by the way can you help me how to create table operator in sql developer