{"id":396,"date":"2019-05-30T01:32:13","date_gmt":"2019-05-30T09:32:13","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=396"},"modified":"2020-06-29T22:32:57","modified_gmt":"2020-06-30T06:32:57","slug":"db2-insert","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-insert\/","title":{"rendered":"Db2 INSERT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 INSERT<\/code> statement to insert a row into a table.<\/p>\n\n\n\n

Introduction to Db2 INSERT statement<\/h2>\n\n\n\n

To insert a new row into a table, you use the INSERT<\/code> statement. The following shows the syntax of the INSERT<\/code> statement:<\/p>\n\n\n

INSERT<\/span> INTO<\/span> \n\ttable_name (column_list)\nVALUES<\/span> \n\t(value_list);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify the name of the table to which you want to insert a new row after the INSERT INTO<\/code> keywords followed by comma-separated column list enclosed in parentheses.<\/li>
  • Then, specify the comma-list of values after the VALUES<\/code> keyword. The values list is also surrounded by parentheses. The order of values in the value list must be corresponding to the order of columns in the column list.<\/li><\/ul>\n\n\n\n
    If you don’t specify a column of a table in the column list, you must ensure that Db2 can provide a value for insertion or an error will occur.<\/p>\n\n\n\n
    Db2 automatically uses the following value for the column that exists in the table but does not specify in the column list of the INSERT<\/code> statement:<\/p>\n\n\n\n
    • The next incremental value if the column is an identity column.<\/li>
    • The default value if the column has a specified default value.<\/li>
    • The NULL<\/code> if the column is defined as a nullable column.<\/li>
    • The computed value if the column is a generated column.<\/li><\/ul>\n\n\n\n
      Db2 INSERT statement examples<\/h2>\n\n\n\n
      The following statement creates a new table named lists<\/code> for the demonstration:<\/p>\n\n\n
      CREATE<\/span> TABLE<\/span> lists(\n\tlist_id INT<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span> NOT<\/span> NULL<\/span>, \n\tlist_name VARCHAR<\/span>(150<\/span>) NOT<\/span> NULL<\/span>,\n\tdescription VARCHAR<\/span>(255<\/span>),\n\tcreated_at TIMESTAMP<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The lists<\/code> table has four columns:<\/p>\n\n\n\n
      • list_id<\/code> is an identity column so that Db2 will provide a sequential integer if you don’t supply a value.<\/li>
      • list_name<\/code> is a varying character column with NOT NULL constraint. The INSERT<\/code> statement must include this column.<\/li>
      • description<\/code> is also a varying character column. However, it is a nullable column which uses NULL as a default value.<\/li>
      • created_at<\/code> column is a TIMESTAMP<\/code> column with a default value of the current timestamp of the operating system on which Db2 instance runs.<\/li><\/ul>\n\n\n\n
        1) Basic INSERT example<\/h3>\n\n\n\n
        The following example uses the INSERT<\/code> statement to insert a new row into the lists<\/code> table:<\/p>\n\n\n
        INSERT<\/span> INTO<\/span> lists(list_name)\nVALUES<\/span>('Daily'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        Here is the output:<\/p>\n\n\n
        1 row inserted.<\/code><\/span><\/pre>\n\n\n
        In this INSERT<\/code> statement:<\/p>\n\n\n\n
        • The identity<\/code> column list_id<\/code> will take a default auto-increment integer.<\/li>
        • The description<\/code> column took null as a default.<\/li>
        • The created_at<\/code> column get the current timestamp at the time of insert.<\/li><\/ul>\n\n\n\n
          After inserting the row into the lists<\/code> table, you can use the following SELECT<\/code> statement to view what the modified table looks like:<\/p>\n\n\n
          SELECT<\/span> \n\tlist_id, \n\tlist_name, \n\tcreated_at\nFROM<\/span> \n\tlists;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          Here is the output:<\/p>\n\n\n\n
          \"Db2<\/figure>\n\n\n\n
          2) Inserting a new and return id example<\/h3>\n\n\n\n
          To return the generated id of the identity column after insert, you use the SELECT FROM INSERT<\/code> statement:<\/p>\n\n\n
          SELECT<\/span> \n\tlist_id\nFROM<\/span> FINAL<\/span> TABLE<\/span>\n(INSERT<\/span> INTO<\/span> lists(list_name)\nVALUES<\/span>('Weekly Digest'<\/span>));\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          \"Db2<\/figure>\n\n\n\n
          The SELECT FROM INSERT<\/code> also allows you to get multiple generated values, for example:<\/p>\n\n\n
          SELECT<\/span> \n   list_id, \n   list_name, \n   created_at \nFROM<\/span> FINAL<\/span> TABLE<\/span> (\n   INSERT<\/span> INTO<\/span> lists(list_name) \n   VALUES<\/span>('Bi-Weekly Digest'<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          \"Db2<\/figure>\n\n\n\n
          3) Inserting default values example<\/h3>\n\n\n\n
          If you specify the column that has a default value in the column list of the INSERT<\/code> statement, you can use the DEFAULT<\/code> keyword in the value list.<\/p>\n\n\n\n
          This example uses DEFAULT<\/code> in the INSERT<\/code> statement to insert a new row into the lists<\/code> table:<\/p>\n\n\n
          INSERT<\/span> INTO<\/span> lists(list_name, created_at)\nVALUES<\/span>('Monthly Digest'<\/span>, DEFAULT<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          \"Db2<\/figure>\n\n\n\n
          In this example, we used the DEFAULT<\/code> keyword so Db2 uses the default value of the created_at<\/code> column to insert.<\/p>\n\n\n\n
          4) Inserting values into the identity column example<\/h3>\n\n\n\n
          Typically, you don’t need to specify a value for the identity column when you insert a new row into the table because Db2 will provide the value.<\/p>\n\n\n\n
          However, in some situations such as data migration, you may want to insert a value into the identity column:<\/p>\n\n\n\n
          See the following INSERT<\/code> statement:<\/p>\n\n\n
          INSERT<\/span> INTO<\/span> lists(list_id, list_name)\nVALUES<\/span>(5<\/span>,'Special Topics'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          The statement works fine because the list_id is declared with the option GENERATED BY DEFAULT AS IDENTITY NOT NULL<\/code>.<\/p>\n\n\n\n
          If you declare the list_id column as GENERATED ALWAYS AS IDENTITY NOT NULL<\/code>, then you will not able to insert a value into this column.<\/p>\n\n\n\n
          In this tutorial, you have learned how to use the Db2 INSERT<\/code> statement to insert a new row into a table.<\/p>\n
          \n\t
          \n\t\t
          Was this tutorial helpful ?<\/div>\n\t\t
          \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
          \n\t\t
          \n\t\t\t
          <\/div>\n\t\t\t\n\t\t\t