Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • What are dimensions in Tableau? data visualisation
  • How to data cleanse a database table Python Data Cleansing
  • How To Create An Empty Dictionary In Python python dictionaries
  • IndexError: single positional indexer is out-of-bounds Index Error
  • how to create an instance of a class class
  • What is data profiling and its benefits? data profiling
  • how to remove unwanted characters from your data R Programming
  • create read update delete using Tkinter class

how to insert data into a table in SQL

Posted on March 9, 2021May 23, 2021 By admin No Comments on 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.

There are many ways to import your data including , one you could use is import a CSV file with an SQL query.

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.

SQL Tags:duplicate key value, INSERT into, null in sql, primary key, SQL insert

Post navigation

Previous Post: ValueError: pattern contains no capture groups
Next Post: how to update records in SQL

Related Posts

  • how to select columns with SQL SQL
  • how to select all records with SQL SQL
  • select rows with a certain value using SQL SQL
  • What is a Primary Key and Foreign Key SQL
  • How to create a calculated field in Tableau data visualisation
  • how to create and drop a table in SQL SQL

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • How to delete a key from a Python dictionary Python
  • How to pass multiple lists to a function and compare Python Functions
  • how to build a machine learning model machine learning
  • IndexError: single positional indexer is out-of-bounds Index Error
  • How To Validate Cell Values In Excel Python
  • How to add a date when a record is created SQL
  • ValueError: pattern contains no capture groups Value Error
  • How to remove unwanted characters Python Data Cleansing

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT