SQL Insert Table Data .
SQL Insert data into Tables
With the INSERT statement you can add data into rows in the tables in the database in two ways:.- With the VALUES keyword.
- With a SELECT statement.
SQL Insert with VALUES keyword
INSERT INTO table_name [(column1 [, column2]...)]
VALUES (constant11 [, constant12]...);INSERT INTO table_name [(column1 [, column2]...)]
VALUES (constant11 [, constant12]...),
(constant21 [, constant22]...),
.... ;Example of of how to inserting data into all Columns with the VALUES keyword:
insert into bookstore2
values ('0201703092','The Practical SQL, Fourth Edition'
,'Judith S. Bowman','Addison Wesley',39);
insert into bookstore2
values ('0672325764','Sams Teach Yourself XML in 21 Days, Third Edition'
,'Steven Holzner','Sams Publishing',49);In the following example, we must know that the table, bookstore, is created in this way:
CREATE TABLE bookstore2 (
ISBN_NO VARCHAR(15) NOT NULL PRIMARY KEY,
SHORT_DESC VARCHAR(100) NULL,
AUTHOR VARCHAR(40) NULL,
PUBLISHER VARCHAR(40) NULL,
PRICE FLOAT NULL
);Example of inserting data into selected columns with the VALUES keyword:
insert into customer (CUSTOMER_ID, FIRST_NAME, LAST_NAME)
values (10001, 'Ricard', 'Nixon');
insert into customer (CUSTOMER_ID, FIRST_NAME, LAST_NAME)
values (10002, 'Robert', 'Redford');
insert into customer (CUSTOMER_ID, FIRST_NAME, LAST_NAME)
values (10003, 'Judith', 'Bowman');In this example, we select some columns from the table, CUSTOMERS, created in this way:
CREATE TABLE CUSTOMER (
CUSTOMER_ID INT NOT NULL ,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
EMAIL VARCHAR(30),
PRIMARY KEY (CUSTOMER_ID)
);SQL Insert with SELECT statement.
INSERT INTO table_name [(insert_column_list)]
SELECT column_list
FROM table_list
WHERE search_condition;Example of inserting data for all columns into a new table with the SELECT statement:
insert into NEWBOOKSTORE
select * from BOOKSTORE;In this example we know that the table, NEWBOOKSTORE, was created with columns of the same type and order as the table, BOOK STORE (but not necessarily with the same names of the columns):
CREATE TABLE NEWBOOKSTORE(
C_ISBN_NO VARCHAR(15) NOT NULL PRIMARY KEY,
SHORT_DESC VARCHAR(100),
AUTHOR varchar(40),
C_PUBLISHER varchar(40),
C_PRICE FLOAT
);Example of inserting data for selected columns into a table using the SELECT statement:
insert into OTHERBOOKSTORE (C_ISBN_NO, DESCRIPTION, AUTHOR)
select ISBN_NO, SHORT_DESC, AUTHOR from BOOKSTORE;In the following example, we know that the table, OTHERBOOKSTORE, is created with these columns (but only 3 of the columns are used):
CREATE TABLE OTHERBOOKSTORE(
C_ISBN_NO VARCHAR(15) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(100),
AUTHOR varchar(40),
PRICE FLOAT
);When we insert data into the table we could include an expression on the last column, PRICE:
insert into OTHERBOOKSTORE
select ISBN_NO, SHORT_DESC, AUTHOR, PRICE*1.5 from BOOKSTORE;During the insertion of data in a table, we could also select the rows with the WHERE clause:
insert into OTHERBOOKSTORE
select ISBN_NO, SHORT_DESC, AUTHOR, PRICE*1.5 from BOOKSTORE
where PRICE>40; 
Next in menu