Inserting data into a table is a fundamental operation in SQL. It allows you to add new records to your database. Oracle SQL provides several methods for inserting data, each with its own advantages and use cases.
Method 1: Using the INSERT INTO Statement
The most common way to insert data into a table is using the INSERT INTO statement.
Syntax:
SQL
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
table_name: The name of the table where you want to insert data.
column1, column2, ...: The columns into which you want to insert values.
value1, value2, ...: The values to be inserted into the corresponding columns.
Example:
INSERT INTO Students (Student_ID, Student_Name, Address, Phone, City, District, State)
VALUES (1, 'John Doe', '123 Main Street', '555-1234', 'New York', 'Manhattan', 'New York');
This statement inserts a new row into the Students table with the specified values.
Method 2: Using the INSERT ALL Statement
The INSERT ALL statement allows you to insert multiple rows into a single table or multiple tables in a single statement.
Syntax:
INSERT ALL
INTO table1 (column1, column2, ...) VALUES (value1, value2, ...)
INTO table2 (column1, column2, ...) VALUES (value1, value2, ...)
...
SELECT ...
FROM ...
WHERE ...;
Example:
INSERT ALL
INTO Students (Student_ID, Student_Name, Address, Phone, City, District, State) VALUES (2, 'Jane Smith', '456 Oak Avenue', '555-5678', 'Los Angeles', 'Beverly Hills', 'California')
INTO Students (Student_ID, Student_Name, Address, Phone, City, District, State) VALUES (3, 'Michael Johnson', '789 Elm Street', '555-9012', 'Chicago', 'Loop', 'Illinois')
SELECT * FROM Students;
This statement inserts two rows into the Students table in a single operation.
Method 3: Using the INSERT INTO SELECT Statement
The INSERT INTO SELECT statement allows you to insert data from the result set of a query into a table.
Syntax:
INSERT INTO new_table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM old_table_name
WHERE condition;
Example:
INSERT INTO Active_Students (Student_ID, Student_Name, Address, Phone, City, District, State)
SELECT Student_ID, Student_Name, Address, Phone, City, District, State
FROM Students
WHERE City = 'New York';
This statement creates a new table named Active_Students and inserts data from the Students table where the City is "New York".
By understanding these different methods, you can effectively insert data into your Oracle database tables to meet your specific requirements.
Comments