The insert query inserts data: new records, into the table.
Syntax
Following is the basic syntax of the INSERT statement −
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...);
where column1, column2 are the column values in the row that is to be inserted.
Example
The following SQL INSERT statement inserts a new row in the Student table, where it inserts values in the columns id, age, first name and, last name.
SQL> INSERT INTO Student VALUES (101, 20, 'Zara', 'Ali');
Syntax 2
Or, you can insert two specific columns by mentioning the column names, as given below −
ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...) VALUES (value1, value2, ...);
Note − Apache Derby automatically calculates values for generated columns. For example, there is no need to pass values for the id column in the student table created earlier in this tutorial. In case your table has generated columns, use syntax2.
Example
ij> INSERT INTO Student(Age, First_Name, Last_Name) VALUES (21, 'Sucharitha' , 'Tyagi'); 1 row inserted/updated/deleted
And, you can also insert two rows using one statement as follows −
ij>INSERT INTO Student(Age, First_Name, Last_Name) VALUES (20, 'Amit', 'Bhattacharya'), (22, 'Rahul', 'Desai'); 2 rows inserted/updated/deleted
You can verify the contents of the table using the SELECT command (we will discuss this command later in this tutorial).
Syntax 3
You can use another query in the insert statement as −
INSERT INTO table_Name Query
Example
Suppose, we have a table named First_Year in the database as shown below with similar columns as in Student table −
ID |AGE |FIRST_NAME |LAST_NAME ----------------------------------------------------------------- 1 |20 |Raju |Pendyala 2 |21 |Bhargav |Prayaga 3 |22 |Deepthi |Yerramilli
You can insert values in this table to the student table using the above syntax as −
ij> INSERT INTO Student (Age, First_Name, Last_Name) SELECT Age, First_Name, Last_Name FROM First_Year; > 3 rows inserted/updated/deleted
After executing all the above insert statements, the Student table will be as follows −
ID |AGE |FIRST_NAME |LAST_NAME ------------------------------------------------------------- 1 |21 |Sucharitha |Tyagi 2 |20 |Amit |Bhattacharya 3 |22 |Rahul |Desai 4 |20 |Raju |Pendyala 5 |21 |Bhargav |Prayaga 6 |22 |Deepthi |Yerramilli
Insert Data using JDBC program
This section teaches you how to insert data in to a table in Apache Derby database using JDBC application.
If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME; password=PASSWORD“
Follow the steps given below to insert data into a table in Apache Derby −
Step 1: Register the driver
To communicate with the database, first of all, you need to register the driver. The forName() method of the class, Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method.
Step 2: Get the connection
In general, the first step we do to communicate to the database is to connect with it. The Connection class represents the physical connection with a database server. You can create a connection object by invoking the getConnection() method of the DriverManager class. Create a connection using this method.
Step 3: Create a statement object
You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create any of these objects using the appropriate method.
Step 4: Execute the query
After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set.
The executeUpdate() method executes queries like INSERT, UPDATE, DELETE. The executeQuery() method to results that returns data etc. Use either of these methods and execute the statement created previously.
Example
Following JDBC example demonstrates how to insert data into a table in Apache Derby using JDBC program. Here, we are connecting to a database named sampleDB (will create if it does not exist) using the embedded driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertData {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:SampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating a table and populating
String query = "CREATE TABLE Employees("
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
//Executing the query
String query = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupthi', 45000, 'Kochin'), "
+ "('Suchatra', 33000, 'Pune'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Trupti', 45000, 'Kochin')";
stmt.execute(query);
System.out.println("Values inserted");
}
}
Output
On executing the above program, you will get the following output −
Values inserted