INSERT

• Data Manipulation using


  1. INSERT

  2. UPDATE

  3. DELETE
• INSERT INTO


  Add a new row to a table
• UPDATE


  Modify the data (rows) in a table when conditions are met
• DELETE FROM


  Delete rows in a table when conditions are met
• INSERT


  Adding data (rows) to the database
• Some uses of INSERT and the syntax


  • To add one row into an existing table
  • To add multiple rows into an existing table
  • To copy one or more rows from another table
  • Insert data only in some columns
• INSERT INTO table (c1, c2, …) VALUES (v1, v2, ...)


  Syntax to add one row
• INSERT INTO table (c1, c2, …) VALUES (v1, v2, ...), (v1, v2, ...)


  Syntax to add multiple rows
• INSERT INTO table1 (c1, c2, …) SELECT (c1, c2, ...) FROM table2 WHERE ...


  Syntax to copy rows from another table
• The number of columns and values must be the same
• Column positions must correspond to value positions
• Non-numeric data must be specified in single quotes (' ')
• To add one row into an existing table


  • INSERT INTO Pet (PetID, NAME, TYPE, Breed, Age) VALUES(108, 'Digger', 'Dog', 'Beagle', 6);
• To add 2 rows into an existing table


  • INSERT INTO Pet (PetID, NAME, TYPE, Breed, Age) VALUES(108, 'Digger', 'Dog', 'Beagle', 6), (109, 'Felix', 'Cat', 'Siamese', 2);
• To copy one row from another table


  • INSERT INTO Pet (PetID, NAME, TYPE, Breed, Age) SELECT PetID, NAME, TYPE, Breed, Age FROM btoh.Pet2 WHERE AGE = 400;
• INSERT INTO table ( NAME, TYPE, Age) VALUES ( 'Digger', 'Dog', 6);


  Inserting partial data, omitted columns should be declared NULL, auto-increment or default value
• There is an error in the statement: INSERT INTO Pet (PetID, NAME, TYPE, Breed, Age) SELECT (PetID, NAME, TYPE, Breed, Age) FROM btoh.Pet2 WHERE AGE = 400;
• There is no error in the statement: INSERT INTO Pet (PetID, NAME, TYPE, Breed, Age) SELECT PetID, NAME, TYPE, Breed, Age FROM btoh.Pet2 WHERE AGE = 400;
• There is no error in the statement: INSERT INTO Pet VALUES(108, 'Digger', 'Dog', 'Beagle', 6);
• INSERT INTO table VALUES (v1, v2, ...)


  Alternative format to add rows without specifying column names, but values must match all columns in the table