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:.
  1. With the VALUES keyword.
  2. With a SELECT statement.

SQL Insert with VALUES keyword

Syntax:
MySQL, Oracle, SQL server, PostgreSQL:
INSERT INTO table_name [(column1 [, column2]...)]
VALUES (constant11 [, constant12]...);
Alternative Syntax for:
MySQL, SQL server, PostgreSQL:
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:

MySQL, Oracle, SQL server, PostgreSQL:
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:

MySQL, Oracle, SQL server, PostgreSQL:
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:

MySQL, Oracle, SQL server, PostgreSQL:
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:

MySQL, Oracle, SQL server, PostgreSQL:
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.

    Syntax:
    MySQL, Oracle, SQL server, PostgreSQL:
    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):

    MySQL, Oracle, SQL server, PostgreSQL:
    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:

    MySQL, Oracle, SQL server, PostgreSQL:
    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):

    MySQL, Oracle, SQL server, PostgreSQL:
    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:

    MySQL, Oracle, SQL server, PostgreSQL:
    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:

    MySQL, Oracle, SQL server, PostgreSQL:
    insert into OTHERBOOKSTORE
    select ISBN_NO, SHORT_DESC, AUTHOR, PRICE*1.5 from BOOKSTORE
    where PRICE>40; 
  • Prev. in menu SQL/Data/DataInsert.php Next in menu
    Prev. in menu SQL/Data/DataInsert.php Next in menu
    © 2010 by Finnesand Data. All rights reserved.
    This site aims to provide FREE programming training and technics.
    Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
    The risk of using this web-site pages or any program codes from this website is entirely at the individual user.