how to insert data into a table in SQL

Estimated reading time: 4 minutes

After building your data using a create statement ? One of the next steps using SQL is to now get your data in, so it can be managed and updated where necessary.

Here we talk about some of the steps involved, and also how an understanding of the primary key is important.

Before all the following updates happen, the table looks like this:

--Inserting one row of data
Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES (456789,'web')

select * from dbo.CUSTOMER

Insert one row into a database table

In the below code we will use the INSERT statement that basically tells the SQL to add a new row to the table.

It is important to remember two things about the primary key:

  • It has to be unique.
  • There can be no null values.

As a result of this been enforced, it will allow tables to be joined together assuming they have the same primary key information.

The output gives us the following:

Inserting multiple rows of data into one table

Following on from the above , we may have a need to insert more than one row.

Again it would follow the same steps, except, we need to ensure all data been inserted follows the same structure and there are no duplicate values in the CUSTOMER_NO column.

Any duplicates in this column would cause it to fail as follows:

It would fail on 125872 and then 456789, both need to be removed from the insert. A sample error message is shown below.

Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES 
 (125872,'web'),
 (333333,'web'),
 (987981,'web'),
 (879784,'web'),
 (654654,'web'),
 (456789, 'Phone')

 select * from dbo.CUSTOMER

Removing the duplicate values from the insert statement, also allows the update to complete successfully:

Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES 
 (333333,'web'),
 (987981,'web'),
 (879784,'web'),
 (654654,'web')

 select * from dbo.CUSTOMER

Insert values into a particular column

There is going to be a scenario, from time to time where you only want to insert values into a particular column, but not all of them.

With this table, as it has a primary key, you can insert new values into CUSTOMER_NO, with no values required for customer_type, as shown below.

If there was no primary key, then you could use the below statement, and in the where clause identify those specific records that need updating.

--Insert values into a particular column
Insert INTO dbo.CUSTOMER(CUSTOMER_NO)
VALUES 
(25897458),
(25898633)

select * from dbo.CUSTOMER

This will yield in the output:

The next section will show you the error , when trying to insert into the customer_type, and leaving CUSTOMER_NO blank.

Inserting a value in without updating the primary key, will fail

Finally we will look at how trying to update a table with a primary key, without providing the primary key value will give you an error.

The code we will use is:

--Inserting a value in without updating the primary key, will fail
Insert INTO dbo.CUSTOMER(customer_type)
VALUES 
('phone'),
('web')

select * from dbo.CUSTOMER

But the table schema states that the CUSTOMER_NO cannot be null:

As a result the following error appears:

In summary we have taken you through a number of different steps , and some of the caveats that you need to understand before inserting data.

The most important thing is that if you have a primary key on a table, doing inserts needs a value supplied that is not a duplicate and also that no empty values for that column are supplied.

how to join tables in SQL

Estimated reading time: 4 minutes

When running an sql select query, most likely we may have a need to bring in data from other tables.

The reason for this , is that tables have a specific purpose for the data they store.

One of the most important features in a data architecture, is to avoid duplication.

This has the impact of only storing data on a table that is required.

As a result joining tables is necessary if you want to get all the information you need .

As an illustration on a customer purchase , the purchase details would be on the purchase table.

BUT the customer details would not be stored there. They would be stored on the customer table.

To put it another way a customer may make many purchases, but you only need their name and address etc. stored once.

The object therefore of joining tables is to use this structure , and avoid duplication over many tables .

For example in the above table, if you had the customer name and address on the purchases table and customer table,

they would always have to be in sync, and it would bring up a maintenance headache for the database administrator.

So what was developed was primary and foreign keys, which helps join tables, based on unique similar values in a column in each table.

What are the types of joins that can be used?

Inner Join: An inner join is where you join two tables but only return the rows that match in both tables.

Left Join: A left join is where you return all the records from the left table, and any matched records from the right table.

Right Join: A right join is where you return all the records from the right table, and any matched records from the left table.

Full Join: A full join uses the left join and right join, and returns all the rows from both tables in the output.

Equally important, where there is no value found a NULL value will be returned.

Lets take some examples to explain the above concepts.

So we have two tables below:

Sales Database, Customer Table

Sales Database, Sales Table

Inner Join

If we run this code, we will return the below. For this reason the purpose of an Inner Join is to only return matched records common to both.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a -- left table
inner join sales.dbo.sales b -- right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Left Join

With a left join of the below code will return four rows. All the records from the left table and any matched records from the right table.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a --left table
left join sales.dbo.sales b --right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Right Join

With a right join of the below code will return three rows. All the records from the right table and any matched records from the left table.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a --left table
right join sales.dbo.sales b -- right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Full Join

Finally in order to run a full join, the result of the below code would be as per the below. The objective is to return all rows matching and unmatching.

In the output, there is a Null Value, meaning that that row has no value for INVOICE_NO which is a primary key.

In essence primary keys are not allowed to have null values.

select a.customer_no, a.customer_type, b.INVOICE_NO from dbo.sales b
full join sales.dbo.customer a
on a.CUSTOMER_NO = b.CUSTOMER_NO

What is a Primary Key and Foreign Key

When working with databases in your data analytics projects, most likely you will deal with more than one table. As a result in certain scenarios there will be a link between the tables

This will ensure that you can use data from the two or more tables that can be joined together. But how would you do that?

In order to join two tables, you will need columns in both tables that have values that are unique and of similar values.

In other words you need to create what is called a primary key, its characteristics are as follows:

  • Its values are unique.
  • They cannot be null.

So where does a foreign key come in? Say we have two tables Table A and Table B.

Table “Customer Table” will have a column that has a primary key call Customer_No

Table “Sales Table” also has a column with a primary key called Sales_No.

  • Now we cant join the two tables, because the primary keys are different, and will have different values
  • But in the Sales Table, we will have an additional column called Customer_No, and it will hold some of the unique values that are held in the Customer Table, Customer_No column.
  • This Customer_No in the Sales Table is called the foreign key. It is unique, has null values and can be connected to a primary key column Customer_No on the Customer Table.

In this way tables can be connected where values in columns are not always the primary key of a table.

So let’s look at tables in SQLite and see how this works in practice.

Below you have a Customer Table and the Sales Table

In both tables, we have a primary key, though they are not the same primary key. As a result, you would not be able to join the tables as the primary keys contained in them do not have the same values, and this is how you would normally join.

In saying that, the two tables can be joined as the foreign key of Sales is related to the primary key of Customer. If invoice_no was not on Sales , then you could make the customer_no in sales the primary key.

So lets look at the tables below with data in them.

Normally when you try to join two tables, it is on the primary key. On the below invoice_no is the primary key of the table Sales, and customer_no is the primary key of the customer table.

While their values in each column are unique, they are not the same , so a join would fail as per the below:

But if you change on the join b.invoice_no TO b.customer_no, it will now bring back all the values asked for. The reason?

The primary key on one is linked to the foreign key on the other, with similar unique values.

As a result, you can still have a primary key column, not in another table and join to that table as long as there is a foreign key column with the same values.

This helps to maintain the table structure, without having to remove primary keys.