Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/sql-insert-statement/ below:

SQL INSERT INTO Statement - GeeksforGeeks

The INSERT INTO statement in SQL is used to add new rows to an existing table, whether for all columns, specific columns or by copying from another table. It is an essential command for populating databases with relevant records like customers, employees, or students.

• Insert data into all or selected columns
• Add one or multiple rows at once
• Copy data from another table
• Populate tables with meaningful records

This method is used when you want to insert data into all columns of a table without specifying column names. We simply provide the values for each column, in the same order that the columns are defined in the table.

Syntax:

INSERT INTO table_name 
VALUES (value1, value2, value); 

Parameters:

• table_name: The name of the table where the data will be inserted
• value1, value2: The values you want to insert into the respective columns of the table

Example: For better understanding, let's look at the SQL INSERT INTO statement with examples. Let us first create a table named 'Student'.

CREATE DATABASE StudentDB;
USE StudentDB;

CREATE TABLE Student (
    ROLL_NO INT PRIMARY KEY,
    NAME VARCHAR(50),
    ADDRESS VARCHAR(100),
    PHONE VARCHAR(15),
    AGE INT
);

INSERT INTO Student (ROLL_NO, NAME, ADDRESS, PHONE, AGE) VALUES
(1, 'Ram', 'Delhi', 'XXXXXXXXXX', 18),
(2, 'Ramesh', 'Gurgaon', 'XXXXXXXXXX', 18),
(3, 'Sujit', 'Rohtak', 'XXXXXXXXXX', 20),
(4, 'Suresh', 'Rohtak', 'XXXXXXXXXX', 18);

Output

If we don’t want to specify the column names (and you’re inserting data into all columns), we can directly insert values in the order they appear in the table structure. Here's an example:

Query:

INSERT INTO Student 
VALUES ('5','HARSH','WEST BENGAL', 'XXXXXXXXXX','19');

Output

In some cases, you might want to insert data into only certain columns, leaving the others empty or with default values. In such cases, we can specify the column names explicitly.

Syntax

INSERT INTO table_name (column1, column2, column3
VALUES ( value1, value2, value); 

Parameters:

• table_name: name of the table. 
• column1, column2..: name of first column, second column.
• value1, value2, value..:  the values for each specified column of the new record.

Example: Let’s say we only want to insert the student's ID, name, and age into the Students table, and leave the address and phone number as NULL (the default value).

INSERT INTO Student (ROLL_NO, NAME, Age) 
VALUES ('5', "PRATIK", 19');

Output:

Note: Columns not included in the INSERT statement are filled with default values (typically NULL).

Instead of running multiple INSERT INTO commands, you can insert multiple rows into a table in a single query. This is more efficient and reduces the number of database operations.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES
(value1, value2, ...),
(value1, value2, ...),
(value1, value2, ...);

Example: If we want to add multiple students to the Students table in one go, the query would look like this:

INSERT INTO Student (ROLL_NO, NAME, AGE, ADDRESS, PHONE) 
VALUES
(6, 'Amit Kumar', 15, 'Delhi', 'XXXXXXXXXX'),
(7, 'Gauri Rao', 18, 'Bangalore', 'XXXXXXXXXX'),
(8, 'Manav Bhatt', 17, 'New Delhi', 'XXXXXXXXXX'),
(9, 'Riya Kapoor', 10, 'Udaipur', 'XXXXXXXXXX');

Output:

Explanation:

• This method is faster than running multiple individual INSERT INTO commands.
• If you're inserting more than 1000 rows, consider using bulk insert or multiple insert statements for efficiency.

We can also copy data from one table into another table using the INSERT INTO SELECT statement. This is very useful when we want to move or replicate data from one table to another without manually typing all the data.

Inserts every column from source table into destination table

INSERT INTO target_table
SELECT * FROM source_table;

Example: If you want to copy all data from the OldStudents table into the Students table, use this query:

INSERT INTO Students
SELECT * FROM OldStudents;

Allows inserting only selected columns from the source table.

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table;

Example: Let’s say we want to copy only the Name and Age columns from OldStudents into Students:

INSERT INTO Students (Name, Age)
SELECT Name, Age
FROM OldStudents;

You can also insert specific rows based on a condition by using the WHERE clause with the SELECT statement.

INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;

Example: If we want to copy only students older than 20 years from OldStudents to Students, we would write:

INSERT INTO Students
SELECT * FROM OldStudents
WHERE Age > 20;

SQL INSERT INTO Statement

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4