Skip to main content
Engineering LibreTexts

16.3..1: Specific INSERT Examples

  • Page ID
    92223
  • \( \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}}\)

    Inserting specific values into an IDENTITY column

    By default, data cannot be inserted directly into an IDENTITY column; however, if a row is accidentally deleted, or there are gaps in the IDENTITY column values, you can insert a row and specify the IDENTITY column value.

    IDENTITY_INSERT option

    To allow an insert with a specific identity value, the IDENTITY_INSERT option can be used as follows.

    SET IDENTITY_INSERT jobs ON
    INSERT INTO jobs  (job_id, job_desc, min_lvl, max_lvl)
    VALUES (19, ’DBA2’, 100, 175)
    SET IDENTITY_INSERT jobs OFF

     Inserting rows with a SELECT statement

    We can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. Consequently, there may be many rows with the same pub_id in the example below.

    This example creates a smaller temporary Publishers table using the CREATE TABLE statement. Then the INSERT with a SELECT statement is used to add records to this temporary Publishers table from the Publishers table.

    CREATE TABLE dbo.tmpPublishers (
    PubID char (4) NOT NULL ,
    PubName varchar (40) NULL ,
    city varchar (20) NULL ,
    province char (2) NULL ,
    country varchar (30) NULL  DEFAULT (‘Canada’)
    )
    INSERT  tmpPublishers
    SELECT * FROM Publishers

    In this example, we’re copying a subset of data.

    INSERT tmpPublishers (pub_id, pub_name)
    SELECT PubID, PubName
    FROM Publishers

    In this example, the publishers’ data are copied to the tmpPublishers table and the country column is set to Canada.

    INSERT tmpPublishers (PubID, PubName, city, province, country)
    SELECT PubID, PubName, city, province, ‘Canada’
    FROM Publishers

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

    • Was this article helpful?