Insert Data

Introduction

Inserting data into a database is one of the most fundamental operations in SQL. The INSERT statement allows you to add new rows of data into a table. Whether you're inserting a single row, multiple rows, or copying data from one table to another, understanding how to effectively use the INSERT statement is crucial for managing and maintaining data in your SQL databases.

In this section, we will explore the INSERT statement, different ways to use it, and practical examples.

Insert into

To insert data into an SQL table, you use the INSERT INTO statement. This statement allows you to add new rows of data to the specified table or columns of a table.

Syntax

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

Example

INSERT INTO Customers (customer_id, first_name, last_name, customer_name,  Email , date_created)
VALUES (1002, 'Alex', 'Martin','alex Martin', 'alex.martin@mail.com','2024-08-31');

This command inserts one new customer record into the Customers table. note values are just added to specified columns rest of the tables will be null.

Using insert into, you can insert multiple rows in one go.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1a, value2a, value3a, ...),
       (value1b, value2b, value3b, ...),
        ...;

Data from one table can be inserted into another using a subquery. This is especially helpful for transferring data across tables or copying data.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE condition;

Note: you insert data into all columns without specifying column names.But you must provide values for all columns in the correct order.

Key Point to keep in mind while using INSERT INTO

• Column Order: The order of columns in the INSERT statement should match the order of the values being inserted.
• Default Values: If you don't provide values for all columns, those columns should either have a default value or be able to accept NULL.
• Constraints: The INSERT statement must comply with any constraints (e.g., NOT NULL, UNIQUE, PRIMARY KEY) defined on the table. Violating these constraints will result in an error.

Select into

This command creates a new table and adds all data from the existing table into the new table.

SELECT INTO is used for:

• Data Backup
• Archiving tables
• Creating temporary tables
• Cloning Tables

Syntax

SELECT  *
INTO new_table
FROM existing_table
WHERE condition;

you can create new table with selected columns and filte the data before inserting into new table.

Example

SELECT customer_id, first_name, last_name, Email,Phone
INTO customer_contact
FROM Customers
WHERE Customer_Status = 'Active';

Key Point to keep in mind while using SELECT INTO

• It creates a new table: SELECT INTO creates a new table that does not exist yet. If the table already exists, this statement will fail.
• It copies data and structure: It copies both the data and the structure (column names, data types, etc.) of the selected columns.
• Performance: Since it creates a new table, SELECT INTO can be slower than INSERT INTO when dealing with large datasets.

Differences Between SELECT INTO and INSERT INTO

Conclusion

Both INSERT INTO and SELECT INTO can be used to insert data; however, INSERT INTO requires an already-existing table, whereas SELECT INTO generates a new table.

Asked in Interview

• How INSERT INTO , SELECT INTO are different?
• How can you insert multiple rows with a single INSERT statement?
• How do you insert data into a table using a SELECT query from another table?
• How would you migrate data from one table to another with different structures?
• How would you insert default values into a table?
• What are the performance implications of the INSERT statement on large tables, and how can they be mitigated?