Once you create a table using CREATE TABLE command by applying the constraints, you are ready to insert data in table. To do this you need the INSERT statement of DML sublanguage of SQL. The INSERT statement can be used to insert data in all the columns or some selected columns.
Syntax of INSERT Statement to Insert Data in a Table
INSERT INTO table-name
- INSERT- keyword
- table-name is the name of the table in which you want to insert data
- column-name1, column-name2 … are the names of the columns against which you want to insert data
- VALUES- keyword
- value-column1, value-column2.. are the values that you must supply to the INSERT statement
Rules to follow to insert data in table
- The column names and the values must match in number and in data types. It means that if you are trying, for example, to insert data in a table for just three columns out of total ten columns then the values after VALUES keyword must also be three in count.
- The data type of the values must match the data type of corresponding columns. Number values are without any quotes. Text data must be enclosed in single quotes for example ‘New York’. Date must be enclosed in quotes with data format of ‘DD-MON-YY’ g. ’01-MAY-19’
Examples of INSERT statement
All Columns of a Table
While inserting data in all columns of a table you don’t need to specify the column names. You must make sure that you give the values of columns in the sequence the columns appear in the table definition.
insert into salesTbl values(1001, ‘Bob Mathews ', Nashville',0.1); insert into salesTbl values(1002,'Cathy Mills','San Diego',0.1); insert into salesTbl values(1003, ‘Alex Rode ', New York ',0.2); insert into customertbl values (102,'Stefan Mook','Ohio','010-8222-1919',8.2); insert into Orderstbl values (1, 101,103,500.25,'01-MAY-19','15-MAY-19');
Selected columns of a table
While inserting data in selected few columns of a table you don’t need to follow the sequence in which the columns exist in the table definition. You can skip the columns from the table and add values in only those columns in which you want to.
insert into customertbl (CustID, CustName, CustCity) values (103,'Sujob Day','Los Angeles'); insert into Orderstbl (ORDERID,CustID, SPID,SaleAMT, OrderDate) values (1, 102,101,1500.25,'01-MAY-19'); insert into salesTbl (SPID, SPNAME,SPCOMM ) values(1003, 'Angela Jones', 0.2);
Points to remember
To Insert Data in Table remember following things
- If you are inserting data in a table with Foreign Key Constraint, you can add only those values in foreign key columns that appear in the corresponding Primary Key Column of the referenced table.
- With one insert statement you can insert a single row of data. To add more data you have to write and run multiple Insert Statements.
- While inserting data in selected columns make sure that you do not forget to include the Primary Key Column.