Skip to main content
Engineering LibreTexts

16.3: INSERT statement

  • Page ID
    92222
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    The INSERT statement adds rows to a table. In addition,

    • INSERT specifies the table or view that data will be inserted into.
    • Column_list lists columns that will be affected by the INSERT.
    • If a column is omitted, each value must be provided.
    • If you are including columns, they can be listed in any order.
    • VALUES specifies the data that you want to insert into the table. VALUES is required.
    • Columns with the IDENTITY property should not be explicitly listed in the column_list or values_clause.

    The syntax for the INSERT statement is:

    INSERT [INTO] Table_name | view name [column_list]
    DEFAULT VALUES | values_list | select statement

    When inserting rows with the INSERT statement, these rules apply:

    • Inserting an empty string (‘ ‘) into a varchar or text column inserts a single space.
    • All char columns are right-padded to the defined length.
    • All trailing spaces are removed from data inserted into varchar columns, except in strings that contain only spaces. These strings are truncated to a single space.
    • If an INSERT statement violates a constraint, default or rule, or if it is the wrong data type, the statement fails and SQL Server displays an error message.

    When you specify values for only some of the columns in the column_list, one of three things can happen to the columns that have no values:

    1. A default value is entered if the column has a DEFAULT constraint, if a default is bound to the column, or if a default is bound to the underlying user-defined data type.
    2. NULL is entered if the column allows NULLs and no default value exists for the column.
    3. An error message is displayed and the row is rejected if the column is defined as NOT NULL and no default exists.

    This example uses INSERT to add a record to the publisher’s Authors table.

    INSERT INTO Authors
    VALUES(‘555-093-467’, ‘Martin’, ‘April’, ‘281 555-5673’, ‘816 Market St.,’ , ‘Vancouver’, ‘BC’, ‘V7G3P4’, 0)

    This following example illustrates how to insert a partial row into the Publishers table with a column list. The country column had a default value of Canada so it does not require that you include it in your values.

    INSERT INTO Publishers (PubID, PubName, city, province)
    VALUES (‘9900’, ‘Acme Publishing’, ‘Vancouver’, ‘BC’)

    To insert rows into a table with an IDENTITY column, follow the below example. Do not supply the value for the IDENTITY nor the name of the column in the column list.

    INSERT INTO jobs
    VALUES (‘DBA’, 100, 175)

    16.3: INSERT statement is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?