SQL INSERT INTO STATEMENT

In RDBMS, we can use the INSERT INTO statement to insert one or more records into a table.

When the database adds a new row, it checks for all the integrity constraints and data types defined in the table. Some of these constraints can be foreign key constraints, primary key constraints, not null constraints. If there are any issues with satisfying these constraints and data types, the database will throw an error and will not insert any new row into the table.

Syntax

There are mainly two types of Syntax for INSERT statements, depending upon whether we are inserting one or multiple records into a table or from an existing table.

Insert new Rows into a table

• Using Column name

Here the number of columns and values being inserted should match, as the database system will match them by their relative position.

INSERT INTO table_name (col1, col2, ...)
VALUES (col1_value, col2_value, ..);

• Without using the Column name

If we don’t specify any column and lots value in the INSERT statement, the database will assign a default value to those columns for that row or rows. These default values can be defined in the table DDL(Data Definition Language) or the database can assign them automatically.

If we are trying to add values for all the columns of the table, we don’t need to specify the names of the columns in the query. But the order of the columns in the table and the values should match.

INSERT INTO table_name 
VALUES (col1_val, col2_val2,..);

Insert new Rows into a table from an existing table

INSERT INTO table_name
(col1, col2, ... )
SELECT expression1, expression2, ...
FROM source_table_name
[WHERE conditions];

In this blog post, we will refer to these tables as an example.

Examples

Let’s take a look at some examples of INSERT INTO statements for inserting rows into a table.

Insert into a table using Column Name

To insert one row into a table, you use the following syntax of the INSERT statement.

INSERT INTO OT.CUSTOMERS_DATA (CUS_ID,NAME,ADDRESS,WEBSITE,CRDT_LMT) VALUES
	 (112,'State Country','700 Hidden Ridge, Irving, TX','http://www.StateCountry.com',800);

Insert Into a table without using the Column name

Below, the INSERT INTO statement shows how a single row can be inserted into a table without giving all the column names.

INSERT INTO OT.CUSTOMERS_DATA  VALUES
	 (113,'State Country','700 Hidden Ridge, Irving, TX','http://www.StateCountry.com',800);

Output

When we execute these statements, the output looks like below.

Inserting rows into a new table using an existing table

Let’s take an example where we are creating a new table called CUSTOMERS_DATA_NEW from an existing table CUSTOMERS_DATA.

	CREATE TABLE "OT"."CUSTOMERS_DATA_NEW" 
   ("CUS_ID" NUMBER  NOT NULL , -- Customer ID
	"NAME" VARCHAR2(255) NOT NULL , 
	"ADDRESS" VARCHAR2(255), 
	"WEBSITE" VARCHAR2(255), 
	"CRDT_LMT" NUMBER(8,2), --  Credit Limit
	 PRIMARY KEY ("CUS_ID"))
;

We will insert records into this table from the existing table whose credit limit is greater than 300.

INSERT INTO OT.CUSTOMERS_DATA_NEW (CUS_ID,NAME,ADDRESS,WEBSITE,CRDT_LMT)
SELECT CUS_ID,NAME,ADDRESS,WEBSITE,CRDT_LMT
FROM OT.CUSTOMERS_DATA cd
WHERE cd.CRDT_LMT >300;

The above query inserted 11 records, which will be validated below.

Conclusion

In this blog post, we learned how to use insert statements to insert new records to an existing or new table using Structured Query Language(SQL) Insert Into statement.