SQL INSERT INTO
DESCRIPTION
The Oracle INSERT statement is used to insert a one of a kind record or multiple records into an Oracle table.
Syntax
The syntax of Oracle INSERT statement when inserting a novel record using the VALUES key quip is as follows:
INSERT INTO THE TABLE (column1, column2, ... column_n)
Values ('expression1', 'expression2',' .. n');
Or the syntax of Oracle INSERT statement when inserting multiple records using a SELECT guidance is as follows:
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];
Settings or restraints
Table
Scene in the one that insert the recordings.
column1, column2, ... column_n
Columns in the scene to insert values.
expression1, expression2, ... expression_n
Values to be assigned to the scene columns. Thus, column 1 would be assigned the expression value1, column2 would be assigned the expression value2, and so on.
source_table
Scene source when inserting data from another table.
Where conditions
Optional. The conditions that must be met for the files to be inserted.
Note :
- When you insert records into a table using the Oracle INSERT statement, you must provide value for each NOT NULL column.
- You can omit a column from the Oracle INSERT statement if the column allows NULL values. Example - Use of the VALUES key quip
- The in addition to straightforward way to create an Oracle INSERT query to list values using the values key quip.
Standard example:
INSERT INTO suppliers (Id, Country) VALUES (18635, 'USA');
This Oracle INSERT guidance would result in a record being inserted into the vendor table. This new record would have a Id of 18635 and a Country of "USA".
Example - Use of the SELECT statement
You can also create Oracle INSERT directions in addition to complicated using SELECT instructions.
Standard example:
INSERT INTO SUPPLIERS
(id, name)
SELECT account , name
Customers
Where customer_id > 5000;
By placing a SELECT guidance in the INSERT statement, you can make product additions quickly.
With this type of insert, you can check the number of lines inserted. You can determine how many lines will be inserted by running the next Oracle SELECT statement before you insert.
SELECT the number
Customers
Where customer_id > 5000;
Frequently asked questions
Question: I'm building a database with customers. I know you're using Oracle INSERT to insert information into the database, but can I make sure I don't add up to the same customer information?
Answer: You can make sure that you don't insert information into twofold using the EXISTS condition.
Standard example, if you had a table named customers with a primary client_id key, you can use the following Oracle INSERT statement:
INSERT IN CUSTOMERS
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, "advertising"
DE Suppliers
Where DOES NOT exist (SELECT
Customers
Where clients.client_id - suppliers.supplier_id);
This Oracle INSERT guidance inserts multiple records with a sub-selection.
If you want to insert a single record, you can use the following Oracle INSERT statement:
INSERT IN CUSTOMERS
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
DE twofold
Where DOES NOT exist (SELECT
Customers
Where clients.client_id - 10345);
Using the twofold table allows you to enter your values into a selected guidance, even if the values are not currently stored in a table.
Question: How can I insert multiple lines of explicit data into an INSERT command in Oracle?
Answer: Here's an example of how you can insert 3 lines into Oracle's vendor table, using Oracle INSERT guidance:
INSERT EVERYTHING
SUPPLIERS INTO (supplier_id, supplier_name) VALUES (1000, 'IBM')
SUPPLIERS INTO (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
SUPPLIERS INTO (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT FROM THE TWOFOLD;
Practice exercise 1:
Based on the contact table, insert a contact folder whose contact_id is 1000, last_name is Smith, first_name is Jane, and the address is 10 Somewhere St.:
CREATE TABLE contacts
(contact_id number(10) not zero,
last_name varchar2(50) not zero,
first_name varchar2(50) not zero,
address varchar2(50),
varchar city2(50),
varchar2(20),
zip_code varchar2(10),
CONSTRAINT PK_CONTACTS PRIMARY KEY (contact_id)
);
Arrangement for practice 1 practice:
The following Oracle INSERT statement would insert this recording into the employee table:
INSERT IN contacts
(contact_id, last_name, first_name, address)
Values
(1000, 'Smith', 'Jane', '10 Somewhere St.');
Practice exercise 2:
Depending on the contacts and customers table, insert all customers residing in the state of Florida into the contact table.
CREATE TABLE contacts
(contact_id number(10) not zero,
last_name varchar2(50) not zero,
first_name varchar2(50) not zero,
address varchar2(50),
varchar city2(50),
varchar2(20),
zip_code varchar2(10),
CONSTRAINT PK_CONTACTS PRIMARY KEY(contact_id)
);
Create TABLE customers
(customer_id number(10) not invalid,
last_name varchar2(50) not zero,
first_name varchar2(50) not zero,
address varchar2(50),
varchar city2(50),
varchar2(20),
zip_code varchar2(10),
CONSTRAINT PK_CUS PRIMARY KEY (customer_id)
);
Arrangement for practice 2 practice:
The following Oracle INSERT statement would insert this record into the vendor table:
INSERT INTO contacts
(contact_id, last_name, first_name, address, city, state, zip_code)
SELECT customer_id, last_name, first_name, address, city, state, zip_code
Customers
Where state - 'Florida';
Since the number of fields in the contacts and customers table is the same and the fields are listed in the same order, you can write the arrangement as follows (although it's usually best to list column names in case table definitions change):
INSERT IN contacts
SELECT
Customers
Where state - 'Florida';
0 Comments