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 www.sqltutorialacademy.com/sql...
Primary Key
www.w3schools.com/sql/sql_pri...
Data Types
www.w3schools.com/sql/sql_dat...
Microsoft Definition of SQL Tables
docs.microsoft.com/en-us/sql/...

[@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.

[@TheSQLGuy]

Thanks for the great comment. Really glad that it helped

[@DublinObserver08]

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.

[@TheSQLGuy]

Thanks, thats a great comment. I'm glad the video has helped

[@schumacher1994]

Very helpful, clear and to the point! Many thanks

[@anthonynorman1212]

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.

[@roywit7836]

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

[@TheSQLGuy]

Glad it helped. Thanks a lot

[@voidthinker]

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

[@TheSQLGuy]

Excellent, thanks a lot. Glad it helped

[@PostHeartBreak]

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

[@TheSQLGuy]

Glad you enjoyed it! Lots more coming soon

[@oratilegodwill4239]

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

[@TheSQLGuy]

Thanks, glad you enjoyed it! More to come soon too

[@samreenfatima2551]

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 😊😊

[@TheSQLGuy]

Glad it helped you! Long may it continue

[@lwazikhanye3344]

VERY useful thank you

[@owosenikehinde5600]

found this very helpful. thanks

[@KanizFatema-qh1rg]

You are a best Teacher, thank you

[@nwanebunkemjika7822]

amazing !!!

[@thanunvsn7345]

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

[@TheSQLGuy]

Glad it helped!

[@skurakox]

Finally, understood what is going on with creating tables

[@TheSQLGuy]

Glad it helped, I've got a few more table related tutorials coming out soon too

[@yougotonelifespenditwisely4390]

finally found a tutorial

[@teamvamospickleball4815]

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

[@chouchoubn5364]

OMG TYSM I DIDNT KNOW ITS THAT EASY🥰🥰

[@arturcarreira8595]

Please, could you help me with a question? What is the diference between Create type [...] as table and create table () ? Thanks

[@jay_with_the_real6381]

Awesome!

[@TheSQLGuy]

Cheers, appreciate it

[@belenavelar2106]

Thanks man!

[@TheSQLGuy]

No probs

[@rosannaasejo6718]

very nice explination. hoping you will upload more video

[@TheSQLGuy]

Thanks, glad you liked it. Yes more coming soon so stay tuned

[@itreportcambodia8283]

video good

[@MrAmrshebl87]

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

[@TheSQLGuy]

Thanks, glad you enjoyed it. I'd turn this into a stored procedure and schedule it. For the query, there are a few ways. You could use a cursor to loop through table 1 and query table 2 and depending on the result it do the next step. Alternatively you could use a Left JOIN to search for any missing values. It depends on a number of factors, cursors aren't great and try to avoid them where possible. It also depends on table size etc but I'd look at those two options

[@UnderSurveillanceSince]

Great presentation

[@TheSQLGuy]

Thanks, glad you enjoyed it

[@Fabian-ew7ly]

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?

[@chalkpitbullwatermelon1897]

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.

[@pratikshadake5311]

Thank you

[@TheSQLGuy]

You're welcome, hope they help

[@flavius95]

Which software you use? Mysql ?

[@Steven-mf2db]

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.

[@TheSQLGuy]

I love tbl_ !!! Any naming convention is better than nothing though so I guess TABLE works 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.

[@HIghlordBalkan]

Closed my connection then reopened it and everything worked. Not quite sure why but I won't complain. That you very much sir.

[@alwinvarghese3307]

How can i insert adress

[@jorgedelacruz6612]

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

[@TheSQLGuy]

I've got a tutorial on SQL Operators. Let me know if that helps

[@crb3488]

LOL "Unless you're five, nobody cares about half years"

[@TheSQLGuy]

Glad my joke made someone laugh!

[@cleverjay286]

The video isn't clear here

[@riffle1564]

Don't know how all have commented but bro it is impossible to see anything what u r displaying u should use Magnifier

[@TheSQLGuy]

Yes, I've changed that with later videos. Thanks for the feedback though

[@fitlalapo6673]

You could have at least zoomed into the screen so we could see what your typing

[@user-qd7du4ok1s]

Video 🎥 and voice is not clear.

[@prasadlad2097]

Knowledge is best but volume is very slow this video

[@twistedinsane3026]

It doesn't work

[@TheSQLGuy]

It does work. Which bit isn't working for you

[@twistedinsane3026]

🤥🤥🤥

[@twistedinsane3026]

🤥🤥🤥

[@twistedinsane3026]

🤥🤥🤥