SQL Values - How to Enter Single and Multiple Values in a Table in SQL?

Discover the art of SQL values insertion with our comprehensive guide. Learn how to seamlessly input single and multiple values into tables with ProjectPro!

Recipe Objective - SQL Values - How to Enter Single and Multiple Values in a Table in SQL?

Structured Query Language (SQL) is a powerful tool for managing and manipulating data within relational databases. When it comes to inserting values into tables, SQL provides several methods to accommodate both single and multiple values. Check out this recipe to explore the various techniques to enter values into a table, catering to specific needs like uniqueness, counting, and updating.

How to Insert Single Values in a Table? 

  1. Basic Syntax

To insert a single value into a table, the basic SQL syntax is as follows:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

For example:

INSERT INTO employees (first_name, last_name, salary)

VALUES ('John', 'Doe', 50000);

This inserts a new record into the 'employees' table with the specified values.

SQL Insert Values into a Table - Example 

Please refer to the previous tutorial to learn how to create a table –
Tables in SQLWe have successfully created a table structure that looks something like –
Customer_id Customer_name Age City Country

The next step is to insert values into the customers table. We will enter the values as shown below.

Customer_id   Customer_name   Age City   Country
101   Thomas Shelby   30   Birmingham   England
102   Grace Burgess   28   Dublin   Ireland
103   Alfie Solomons   40   London   England
104   Michael Gray   22   New York   USA
105   May Carleton   29   Sheffield   England

You can insert values into the table using the INSERT statement. You can –

Insert values without mentioning the columns
Insert values while mentioning the columns

Let us see both of these methodologies.

1) Insert values without mentioning the columns

Syntax:
INSERT INTO table_name VALUES(value1, value2,...);

You must enter values in a sequence that matches the column sequence.

Code:

INSERT INTO customers VALUES (101,"Thomas Shelby",30,"Birmingham","England");

SQL Insert into values

2) Insert values while mentioning the columns

Syntax:
INSERT INTO table_name(column_name1,column_name2,...) VALUES (value1,value2,...)

The sequence of values must match the column sequence. You can enter the customer_id in the second place, but make sure that you mention customer_id in the second place as well in the columns list.

Code:

INSERT INTO customers(customer_name,customer_id, age,city,country)

VALUES("Grace Burgess",102, 28,"Dublin","Ireland");

SQL Insert into values

How to insert multiple values into a table?

You can enter multiple values into a table using one single query as follows:-

Code:

INSERT INTO customers (customer_id,customer_name ,age,city,country)

VALUES (103,"Alfie Solomons", 40,"London","England"),

        (104,"Michael Gray",22,"New York","USA"),

        (105,"May Carleton",29,"Sheffield","England");

SQL Insert multiple values

SQL Insert Multiple Values Using SELECT

Another approach is to use the SELECT statement to fetch values from another table and insert them into the target table:

INSERT INTO table_name (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM source_table

WHERE condition;

Become a SQL expert with ProjectPro's hands-on learning. Elevate your career prospects and conquer the world of data management.

Unique Values and Counting

How to Ensure SQL Unique Values?

To maintain uniqueness in a column, you can use the UNIQUE constraint when defining the table:

CREATE TABLE table_name (

    column1 datatype UNIQUE,

    column2 datatype,

    ...

);

This ensures that each value in column1 is unique.

SQL Count Unique Values

To count the number of unique values in a column, you can use the COUNT DISTINCT function:

SELECT COUNT(DISTINCT column_name) FROM table_name;

For example:

SELECT COUNT(DISTINCT employee_id) FROM employees;

This retrieves the count of unique employee IDs in the 'employees' table.

SQL Update Values

To update existing values in a table, you can use the UPDATE statement:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

For example:

UPDATE employees

SET salary = 55000

WHERE last_name = 'Doe';

This updates the salary for employees with the last name 'Doe' to 55000.

Enhance your SQL Skills with ProjectPro!

This recipe has covered the basics of entering single and multiple values into a table in SQL. Whether you're dealing with unique values, counting distinct values, or updating existing values, SQL provides a flexible set of tools to meet your database management needs. Learning to enter single and multiple values in a SQL table is a fundamental skill for any aspiring database professional. However, true proficiency comes not just from theoretical knowledge but from practical experience. By embracing real-world projects, you can solidify your understanding and enhance your SQL skills. ProjectPro emerges as the ideal companion on this learning journey, offering a comprehensive platform with a repository of over 270+ projects rooted in data science and big data. Elevate your SQL expertise with ProjectPro and empower yourself to tackle complex challenges in the dynamic realm of database management.

What Users are saying..

profile image

Jingwei Li

Graduate Research assistance at Stony Brook University
linkedin profile url

ProjectPro is an awesome platform that helps me learn much hands-on industrial experience with a step-by-step walkthrough of projects. There are two primary paths to learn: Data Science and Big Data.... Read More

Relevant Projects

Build PowerBI Dashboard for Water Quality Sensor Data Analysis
In this PowerBI Project, you will learn to build a PowerBI Dashboard to analyze and visualize water quality sensor data from various European countries.

Build Regression (Linear,Ridge,Lasso) Models in NumPy Python
In this machine learning regression project, you will learn to build NumPy Regression Models (Linear Regression, Ridge Regression, Lasso Regression) from Scratch.

Build CNN for Image Colorization using Deep Transfer Learning
Image Processing Project -Train a model for colorization to make grayscale images colorful using convolutional autoencoders.

AWS Project to Build and Deploy LSTM Model with Sagemaker
In this AWS Sagemaker Project, you will learn to build a LSTM model on Sagemaker for sales forecasting while analyzing the impact of weather conditions on Sales.

LLM Project to Build and Fine Tune a Large Language Model
In this LLM project for beginners, you will learn to build a knowledge-grounded chatbot using LLM's and learn how to fine tune it.

Time Series Analysis with Facebook Prophet Python and Cesium
Time Series Analysis Project - Use the Facebook Prophet and Cesium Open Source Library for Time Series Forecasting in Python

Build Regression Models in Python for House Price Prediction
In this Machine Learning Regression project, you will build and evaluate various regression models in Python for house price prediction.

End-to-End Speech Emotion Recognition Project using ANN
Speech Emotion Recognition using RAVDESS Audio Dataset - Build an Artificial Neural Network Model to Classify Audio Data into various Emotions like Sad, Happy, Angry, and Neutral

Time Series Classification Project for Elevator Failure Prediction
In this Time Series Project, you will predict the failure of elevators using IoT sensor data as a time series classification machine learning problem.

Learn Object Tracking (SOT, MOT) using OpenCV and Python
Get Started with Object Tracking using OpenCV and Python - Learn to implement Multiple Instance Learning Tracker (MIL) algorithm, Generic Object Tracking Using Regression Networks Tracker (GOTURN) algorithm, Kernelized Correlation Filters Tracker (KCF) algorithm, Tracking, Learning, Detection Tracker (TLD) algorithm for single and multiple object tracking from various video clips.

OSZAR »