insert into - sql insert into - sql insert into select. SQL

SQL Insert Into — Everything Has Its Place

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. The SQL Insert operation pushes data into those cells, or, in the SQL parlance: “populates the database.” Two syntax variants exist to insert values into tables by row. The first inserts values into an entire row:

INSERT INTO
  table_name
VALUES
  ( value1, value2, value3, ... );

the second inserts values into specific columns:

INSERT INTO
  table_name ( column1, column2, column3, ... )
VALUES
  ( value1, value2, value3, ... );

This blog post explores some of the ways to use SQL Insert Into.

Your Client Really Likes To Entertain

Let’s consider the use case of creating a table for an over-the-top dinner. You’ve secured a client who wants to pull out all the stops and entertain his audience with a dinner to end all dinners. We begin by having the table store the menu item and an associated wine pairing.

We’ll have to be prepared for all the courses: hors d’oeuvre, amuse-bouche, soup, appetizer, salad, fish, first main course, palate cleanser, second main course, cheese course, dessert, and mignardise.

CREATE TABLE meal (
  hors_doeuvre VARCHAR(99),
  hors_doeuvre_pair VARCHAR(99),
  amuse_bouche VARCHAR(99),
  amuse_bouche_pair VARCHAR(99),
  soup VARCHAR(99),
  soup_pair VARCHAR(99),
  appetizer VARCHAR(99),
  appetizer_pair VARCHAR(99),
  salad VARCHAR(99),
  salad_pair VARCHAR(99),
  fish VARCHAR(99),
  fish_pair VARCHAR(99),
  first_main_course VARCHAR(99),
  first_main_course_pair VARCHAR(99),
  palate_cleanser VARCHAR(99),
  palate_cleanser_pair VARCHAR(99),
  second_main_course VARCHAR(99),
  second_main_course_pair VARCHAR(99),
  cheese_course VARCHAR(99),
  cheese_course_pair VARCHAR(99),
  dessert VARCHAR(99),
  dessert_pair VARCHAR(99),
  mignardise VARCHAR(99),
  mignardise_pair VARCHAR(99)
);

While this example may seem overblown, it’s just the start of real-world projects. For example, a restaurant version of this solution might include a table for each menu item which in turn includes cells containing the major ingredients, suppliers, target prices, quantities in stock, etc. Then maintaining the supply chain and profitability of an ongoing operation lends itself to machine learning for just-in-time inventory management, AI for finding popularity patterns by day of week, holidays, etc.

SQL Insert Into All The Column Cells

The most basic SQL Insert Into syntax populates every column cell (so that specifying column names becomes unnecessary).

INSERT INTO meal
VALUES
  (
    'Goat cheese crostini with fig-olive tapenade',
      'White Bordeaux',
    'Sweet potato chips with goat cheese and caviar',
      'Chablis',
    'Pumpkin sage bisque',
      'Madeira',
    'Mushrooms stuffed with Pecorino Romano, garlic, and bread crumbs',
      'Red Moselle',
    'Chopped Thai salad with peanut dressing',
      'Beaujolais',
    'Crispy trout with a parsley-caper vinaigrette',
      'Dry Rhine',
    'Roasted duck with an orange-ginger glaze',
      'Bordeaux',
    'Mint sorbet',
      'water',
    'Herb-crusted venison medallions',
      'Red Burgundy',
    'Aged, soft, firm, and blue cheeses',
      'Sauterne',
    'Bodino flourless chocolate cake',
      'Sweet Port',
    'Splintered chocolates',
      'Muscatel'
) ;

Passing along the night’s first main course and pairing to the sous-chef and sommelier then becomes:

SELECT
  first_main_course, first_main_course_pair
FROM
  meal ;
first_main_coursefirst_main_course_pair
Roasted duck with an orange-ginger glazeBordeaux

SQL Insert Into Specified Column Cells

Defensive programming proponents would explicitly specify the column names to ensure the SQL Insert Into code continues to work properly when new columns are added to the table.

INSERT INTO meal ( hors_doeuvre, hors_doeuvre_pair, amuse_bouche, 
  amuse_bouche_pair, soup, soup_pair, appetizer, appetizer_pair, 
  salad, salad_pair, fish, fish_pair, first_main_course, 
  first_main_course_pair, palate_cleanser, palate_cleanser_pair, 
  second_main_course, second_main_course_pair, cheese_course, 
  cheese_course_pair, dessert, dessert_pair, mignardise, mignardise_pair )
VALUES ...

SQL Insert Into Another Table

The garde manger is responsible for, among other duties, the salad preparation. To that end they maintain a table where previous salads and pairings are recorded:

CREATE TABLE salads (
  salad VARCHAR(99),
  salad_pair VARCHAR(99)
) ;

Copying the day’s salad offering and pairing into this new table becomes:

INSERT INTO salads
SELECT salad, salad_pair
FROM meal ;

Or, defensively, listing the column names to ensure continued operation even if rows are added to the table:

INSERT INTO salads ( salad, salad_pair )
SELECT salad, salad_pair
FROM meal ;

Perhaps the garde manger only wants to include those salads which contain a particular ingredient. Adding a WHERE clause to the SQL Insert Into accomplishes this filtering:

INSERT INTO salads ( salad, salad_pair )
SELECT salad, salad_pair
FROM meal
WHERE meal.salad LIKE '%walnut%' ;

Updating Existing Information

Please consider reading the SQL Update blog entry, which covers the related function of updating existing table information. For example, perhaps the sommelier has found a better choice for salads with a Caesar dressing:

UPDATE
    meals
SET
    salad_pair = 'Pouilly Fumé'
WHERE
    salad LIKE '%Caesar%' ;

Even More SQL Insert Into — Extending The Model

One way this model would be extended in a commercial offering would be adding a SQL DATETIME data type column and allowing the meals table to keep a record of every meal offered. (This is especially helpful because chefs keep track of which meals are popular by time, day of week, holidays, etc.) 

Conclusion

Covered here, in the context of a real-world programming use case, is the use of the SQL Insert Into statement, including:

  • The syntax for Insert Into all the cells in row and Insert Into select columns
  • Copying into another table (a SQL Insert Into within a SQL Select statement)
  • Updating existing information after the initial Insert Into
  • How this use case could be extended, should the reader want to work on a deeper understanding of these concepts

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE meal (
  hors_doeuvre VARCHAR(99),
  hors_doeuvre_pair VARCHAR(99),
  amuse_bouche VARCHAR(99),
  amuse_bouche_pair VARCHAR(99),
  soup VARCHAR(99),
  soup_pair VARCHAR(99),
  appetizer VARCHAR(99),
  appetizer_pair VARCHAR(99),
  salad VARCHAR(99),
  salad_pair VARCHAR(99),
  fish VARCHAR(99),
  fish_pair VARCHAR(99),
  first_main_course VARCHAR(99),
  first_main_course_pair VARCHAR(99),
  palate_cleanser VARCHAR(99),
  palate_cleanser_pair VARCHAR(99),
  second_main_course VARCHAR(99),
  second_main_course_pair VARCHAR(99),
  cheese_course VARCHAR(99),
  cheese_course_pair VARCHAR(99),
  dessert VARCHAR(99),
  dessert_pair VARCHAR(99),
  mignardise VARCHAR(99),
  mignardise_pair VARCHAR(99)
) ;
CREATE TABLE salads (
  salad VARCHAR(99),
  salad_pair VARCHAR(99)
) ;

INSERT INTO meal
VALUES (
  'Goat cheese crostini with fig-olive tapenade',
    'White Bordeaux',
  'Sweet potato chips with goat cheese and caviar',
    'Chablis',
  'Pumpkin sage bisque',
    'Madeira',
  'Mushrooms stuffed with Pecorino Romano, garlic, and bread crumbs',
    'Red Moselle',
  'Chopped Thai salad with peanut dressing',
    'Beaujolais',
  'Crispy trout with a parsley-caper vinaigrette',
    'Dry Rhine',
  'Roasted duck with an orange-ginger glaze',
    'Bordeaux',
  'Mint sorbet',
    'water',
  'Herb-crusted venison medallions',
    'Red Burgundy',
  'Aged, soft, firm, and blue cheeses',
    'Sauterne',
  'Bodino flourless chocolate cake',
    'Sweet Port',
  'Splintered chocolates',
    'Muscatel'
) ;