How to insert into table in SQL | Insert into table using multiple ways

How to insert into table in SQL :

In Previous article we have get the idea about data defnition language.In this article i will try to give brief idea about Data Manipulation Language in SQL.Data manipulation language is nothing but SQL language with using which one can manipulate data.Using data definition language we able to define a data we cant manipulate data using DDL.In this article you will get proper idea about How to insert into table in SQL. There are various types of methods and syntax for How to insert into table in SQL.First we look in to the Data Manipulation Language commands then we will go for our main topic which is  How to insert into table in SQL 

Following are Data Manipulation Language commands:

1.Update:Update statement of SQL are used to update the records from the table

2. Insert: Insert statement of SQL are used to insert the records in the table.

3. Delete : Delete statement in SQL are used to delete the records from the table.

Insert Statement :

User can insert the data in to specified table using insert into statement.User can insert the data in to table using multiple ways.The INSERT INTO Statement is used to insert new records in a Table. It is possible to write the INSERT INTO Statement using 3 Types:

How to insert into table using multiple ways :

Type1: Without using column name 

The First Form does not specify the Column Names where the Data will be inserted, we can directly enter their values BUT all the Column Values should be entered otherwise it will throw an error.This is most common way to insert records in to table.

INSERT INTO <Table_Name>

VALUES (Value1, Value2, Value3, Value4, Value5);

Example:

INSERT INTO Student

Values (101,’Amit’,’S’,10,’11-OCT-89′,’Tech M’,12345);

Type 2: With using column names:

The Second Form specifies both the Column Names and the Values to be inserted:

Syntax:

INSERT INTO <Table_Name> (Column1, Column2, Column3, Column4, Column5)

VALUES (Value1, Value2, Value3, Value4, Value5);

Example:

INSERT INTO Student (RollNo, FName, LName, Class, DOB, Gender, Location, PhoneNos)

Values (102,’Amit’,’S’, 10,’10-Jun-90′,’Male’,’Kothrud’, 54264);

Tip:

 “You can also specify Fewer Columns and their Values instead of ALL Columns while using insert with column names…”

Example:

INSERT INTO Student (RollNo, FName, LName, Gender)

Values (103,’Pradnya’,’K’,’Female’);

Type3 : Insert Data without using columns multiple time

If you want to enter Multiple Columns Values without writing column name always.

Syntax:

INSERT INTO <Table_Name>

Values (&Column1, &Column2, &Column3, &Column4, &Column5);

Example :

INSERT INTO Student

Values (&RollNo, &FName, &LName, &Class, &DOB, &Location);

Once you Enter all the Column values, Enter / to Re-rerun and enter the values again. You cannot specify fewer columns; need to specify all the columns from the table.

Type 4 : Insert data in to Table using SELECT statement : 

INSERTING Data to a Table through a SELECT Statement can be done in 2 ways:

1) If you are inserting data to all the columns, the Insert Statement can be written as:

Syntax:

INSERT INTO <Table_Name>

SELECT * FROM <Table_Name>;

Example:

INSERT INTO Student_Temp

SELECT * FROM Student;

2) If you are inserting data to specified columns, the Insert Statement can be written as:

Syntax:

 INSERT INTO <Table_Name> [(Column1, Column2,…ColumnN)]

SELECT Column1, Column2,…ColumnN FROM <Table_Name> [WHERE condition];

Example:

 INSERT INTO Student_TEMP1 (RollNo, FName, LName, Class)

SELECT RollNo, FName, LName, Class FROM Student;

Using where clause:

INSERT INTO Student_TEMP1 (RollNo, FName, LName)

SELECT RollNo, FName, LName FROM student

WHERE Gender=’Male’;

Following are some Rules to insert data with select statement: 

1) Column Names should be in the Same Order & Data_Type should also be the same.

2) When adding a new row, you should ensure the Data_Type of the Value and the Column matches.

3) You follow the Integrity Constraints, if any, defined for the Table.

Update Statement in SQL:

The UPDATE Statement is used to Update/Modify existing records in a Table.

Syntax:

 UPDATE <Table_Name>

SET Column1=Value1, Column2=Value2,…ColumnN=ValueN

WHERE Some_Column=Some_Value;

Example:

UPDATE Student

SET Class=1, DOB=’10-FEB-90′

WHERE RollNo=106;

Tip:

“The WHERE Clause specifies which record or records that should be updated. If you Omit the WHERE clause, all records will be updated!”

Example:

UPDATE Student_Temp

SET Gender=’N/A’;

Delete Statement in SQL:

The DELETE Statement is used to delete rows in a Table.

Syntax:

DELETE FROM <Table_Name>

WHERE Some_Column=Some_Value;

Example:

DELETE FROM Student_Temp

WHERE RollNo=105;

The WHERE Clause specifies which record or records that should be Deleted. If you omit the WHERE Clause, all records will be deleted!

Delete All Data:

It is possible to delete all rows in a Table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

Syntax:

DELETE FROM <Table_Name>;

Example:

DELETE FROM Student_Temp;

Hope you get the idea about Data Manipulation Language statements as well as How to insert into table using multiple ways in SQL. If you find this article helpful dont forget to comment in comment section.

HOME