{"id":413,"date":"2019-05-30T23:27:35","date_gmt":"2019-05-31T07:27:35","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=413"},"modified":"2020-04-11T06:59:22","modified_gmt":"2020-04-11T14:59:22","slug":"db2-insert-into-select","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-insert-into-select\/","title":{"rendered":"Db2 INSERT INTO SELECT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 INSERT INTO SELECT<\/code> statement to copy data from a table to another table.<\/p>\n\n\n\n

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

The INSERT INTO SELECT<\/code> statement insert rows returned by a SELECT<\/a><\/code> statement into a table.<\/p>\n\n\n\n

The following shows the syntax of the INSERT INTO SELECT<\/code> statement:<\/p>\n\n\n

INSERT<\/span> INTO<\/span> table_name (column_list)\nSELECT<\/span>-statement<\/span>;\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 target table to which the rows will be inserted and a list of columns.<\/li>
  • Second, use a SELECT<\/code> statement to query data from another table. The SELECT<\/code> statement can be any valid query that returns zero or more rows. The number of columns and their data type must match with the column list in the INSERT INTO<\/code> clause.<\/li><\/ul>\n\n\n\n
    We often use the INSERT INTO SELECT<\/code> statement to copy data from a table to another table; or to insert summary data of a table into another table.<\/p>\n\n\n\n
    Db2 INSERT INTO SELECT examples<\/h2>\n\n\n\n
    1) Insert rows from a table into another table example<\/h3>\n\n\n\n
    Let’s create a table named simple_lists<\/code> for the demonstration:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> simple_lists(\n\tlist_id INT<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n\tlist_name VARCHAR<\/span>(150<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following statement uses the INSERT INTO SELECT<\/code> statement to insert rows whose values in the list_name<\/code> column which contains the keyword \"Db2\"<\/code> into the simple_lists<\/code> table:<\/p>\n\n\n
    INSERT<\/span> INTO<\/span> \n\tsimple_lists (list_name) \nSELECT<\/span>\n\tlist_name\nFROM<\/span>\n\tlists\nWHERE<\/span> \n\tlist_name LIKE<\/span> '%Db2%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returned the following message:<\/p>\n\n\n
    2 rows inserted.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It means that two rows have been inserted into the simple_lists<\/code> table successfully.<\/p>\n\n\n\n
    To verify data in the simple_list<\/code> table after insert, you can use the following SELECT<\/code> statement:<\/p>\n\n\n
    SELECT<\/span>\n\t*\nFROM<\/span>\n\tsimple_lists;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) Insert summary rows from a table into another table<\/h3>\n\n\n\n
    First, create a book_stats<\/code> that stores the book id, title and the number of authors of the book:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> book_stats (\n\tbook_id INT<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>,\n\ttitle VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n\tauthors<\/span> INT<\/span> NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, use the INSERT INTO SELECT<\/code> to query data from the books<\/code> and author_books<\/code> table and insert this summary data into the book_info<\/code> table:<\/p>\n\n\n
    INSERT<\/span> INTO<\/span> \n  book_stats(book_id, title, authors<\/span>)\nSELECT<\/span> \n\tb.book_id,\n\tb.title, \n\tCOUNT<\/span>(a.author_id) author_count\nFROM<\/span> \n\tbooks b\n\tINNER<\/span> JOIN<\/span> book_authors a \n\t\tON<\/span> a.book_id = b.book_id\nGROUP<\/span> BY<\/span> \n  b.book_id, \n  b.title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, verify the data in the book_stats<\/code> table:<\/p>\n\n\n
    SELECT<\/span> \n\tbook_id, \n\ttitle, \n\tauthors<\/span> \nFROM<\/span> \n\tbook_stats\nORDER<\/span> BY<\/span> \n\tauthors<\/span> DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following picture shows the output:<\/p>\n\n\n\n
    \"Db2<\/figure>\n\n\n\n
    In this tutorial, you have learned how to use the Db2 INSERT INTO SELECT<\/code> statement to insert rows returned by a SELECT<\/code> statement 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