Create Tables

Create Table

We will create some brand new tables in SQL Management Studio using Excel as a template in this tutorial.

The sample data is a fictitious list of Experts and their areas of specialty.

Below is a quick Description of our tables and a short sampling of each. This tutorial focuses only on the creation of the tables.

We will populate the tables in the next tutorial. However, it’s good to preview the information so that we understand why we set these up the way we do.

Expert table – Stores the ID, Name, and Birthday of our Experts

Expert table

Technology Table – Stores the Technology Names and basic Information about the technology

Technology Table

Expert Technology Table – Shows which Technologies the Experts are proficient in

Expert Technology Table

It’s fun to play with information in Excel, but we really want to get this information into SQL because Management there is more optimal.

Create Tables in SQL

Let’s create these three tables in our test database so that we can import data in our Next lesson.

Here is the syntax of an SQL create Statement.

Create Table Table_name (
Column1 datatype,
Column2 datatype,
Column3 datatype

Pretty Straightforward right?

The first table to create is our Expert table.

For this exercise, we will use Excel to format the statements for Import to SQL.

Use a separate tab to create your scripts.

create your scripts

Real Quick, these are the Excel functions we will use to put our scripts together.

& Signs – To String Together parts of the statement

“ “Quotes – To Specify parts that are text

CONCAT Symbol – To Pull the entire string together

Notice the formula in Table “column 1” (Column D) of our Excel spreadsheet.

It says, pull the values B5 and B4, our Column names, and data types.

=Sheet1!B5&” “&Sheet1!B4&”,”

Parts that are enclosed in quotations are text.

The & symbol allows us to link parts together when we switch between cell references and text.

Here is the information called from our Create Scripts tab.
Create Scripts tab

Drag the formula from D through our last populated column. H, in this case.

Dragging the formulas will format our additional Column Names and assign them the proper data types.

In addition to our Assigned Columns, there are “System Columns for each”:

Modified by – Displays who updated a record

Timestamp – Displays the time that the record was last updated

Rowcount can increment to reflect each time the row was updated

The aforementioned system columns are not required, but best practice tracking changes to SQL tables.

Use the Concat Function to String it all together.

=CONCAT(A4:L4)

Here’s our final script, the result of concatenating our results.

Create Table expert(Expert_ID Int,First_Name Varchar(20),Last_Name Varchar(20),Birth_date Date,Modified_By Varchar(20),Timestamp Datetime,Rowcount int;

Now you can paste it into your favorite SQL Editor. For our demo, we will use the fan-favorite SSMS.

SSMS

Run the Select Statement to check our results

 

insert Values

The best way to see if the table is refreshed is to click the “refresh” button in the object Explorer and see if a new Table was created.

TestExpert, Confirmed!!

Text Expert

Now take the concatenated scripts for tables 2 and 3 and run them in SSMS.

Create Table expertTech(Exp TECH ID Int,Expert ID Int,Tech Name Varchar(20),Technology Specialty Int,Specialty Code Varchar(5),Modified_By Varchar(20),Timestamp Datetime,Rowcount int;

Create Table technology(Technology ID Int,Short Tech Name Varchar(5),App Name Varchar(30),Category Varchar(20),Modified_By Varchar(20),Timestamp Datetime,Rowcount int;

Now that’s how you create tables in SQL! Stay tuned for the next tutorial where we populate these tables.

keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Share This