Upgrade to Pro — share decks privately, control downloads, hide ads and more …

All About Insert

All About Insert

A back-to-basics session on using INSERT in Oracle Database

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

May 21, 2021
Tweet

Transcript

  1. Ask TOM Office Hours All about INSERT Back to basics

    Chris Saxon, Oracle Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL Your SQL Office Hours begins soon…
  2. insert into <table> ( col1, col2, … ) values (

    val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  3. insert into <table> ( col1, col2, … ) values (

    val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Optional
  4. create table t ( c1 int, c2 int ) insert

    into t values ( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Assumes every column*
  5. alter table t add c3 int; insert into t values

    ( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-00947: not enough values
  6. Ryan McGuire / Gratisography My table has 100 columns! Copyright

    © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  7. insert into t ( c1, c2, c3, c4, c5, c6,

    c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89, c90, c91, c92, c93, c94, c95, c96, c97, c98, c99, c100 ) values ( v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28, v29, v30, v31, v32, v33, v34, v35, v36, v37, v38, v39, v40, v41, v42, v43, v44, v45, v46, v47, v48, v49, v50, v51, v52, v53, v54, v55, v56, v57, v58, v59, v60, v61, v62, v63, v65, v64, v66, v67, v68, v69, v70, v71, v72, v73, v74, v75, v76, v77, v78, v79, v80, v81, v82, v83, v84, v85, v86, v87, v88, v89, v90, v91, v92, v93, v94, v95, v96, v97, v98, v99, v100 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  8. insert into t ( c1, c2, c3, c4, c5, c6,

    c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89, c90, c91, c92, c93, c94, c95, c96, c97, c98, c99, c100 ) values ( v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28, v29, v30, v31, v32, v33, v34, v35, v36, v37, v38, v39, v40, v42, v41, v43, v44, v45, v46, v47, v48, v49, v50, v51, v52, v53, v54, v55, v56, v57, v58, v59, v60, v61, v62, v63, v65, v64, v66, v67, v68, v69, v70, v71, v72, v73, v74, v75, v76, v77, v78, v79, v80, v81, v82, v83, v84, v85, v86, v87, v88, v89, v90, v91, v92, v93, v94, v95, v96, v97, v98, v99, v100 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  9. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Use %rowtype Ryan McGuire / Gratisography
  10. declare tab_rec tab%rowtype; begin … insert into tab values tab_rec;

    end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  11. declare tab_rec tab%rowtype; begin … insert into tab values tab_rec;

    end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL These match
  12. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about defaults? Ryan McGuire / Gratisography
  13. create table t ( t_id int default s.nextval primary key,

    insert_date date default sysdate ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  14. insert into t ( t_id ) values ( default );

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Both columns have default
  15. insert into t ( t_id ) values ( null );

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01400: cannot insert NULL into ("CHRIS"."T"."T_ID")
  16. declare t_rec t%rowtype; begin insert into t values t_rec; end;

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01400: cannot insert NULL into ("CHRIS"."T"."T_ID")
  17. alter table t modify ( t_id default on null s.nextval,

    insert_date default on null sysdate ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  18. Ryan McGuire / Gratisography What was the default value? Copyright

    © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  19. declare t_rec t%rowtype; begin insert into t values t_rec returning

    t_id, insert_date into t_rec.t_id, t_rec.insert_date; end; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  20. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  21. Ryan McGuire / Gratisography I have inserts with no column

    list! Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  22. alter table t add invis_col int invisible; insert into t

    values ( 1, 2 ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Hidden from generic access
  23. declare t_rec t%rowtype; begin t_rec.invis_col := 1; end; Copyright ©

    2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL PLS-00302: component 'T_REC.C3' must be declared Invisible columns excluded
  24. alter table t add ( insert_month as ( trunc (

    insert_date, 'mm' ) ) ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Virtual column Calculated at runtime
  25. declare t_rec t%rowtype; begin insert into t values t_rec; end;

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-54013: INSERT operation disallowed on virtual columns
  26. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Insert into a view Ryan McGuire / Gratisography
  27. create view vw as select t_id, invis_col, insert_date from t;

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Exclude virtual, include invisible
  28. declare t_rec vw%rowtype; begin insert into vw values t_rec; end;

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  29. create view vw as select t_id, invis_col, insert_date from t

    where insert_date > sysdate; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only future rows?!
  30. declare t_rec vw%rowtype; begin insert into vw values t_rec; end;

    select * from vw no rows selected Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  31. create view vw as select t_id, invis_col, insert_date from t

    where insert_date > sysdate with check option; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Stop DML => where not true
  32. declare t_rec vw%rowtype; begin insert into vw values t_rec; end;

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL ORA-01402: view WITH CHECK OPTION where-clause violation
  33. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What about complex views? Ryan McGuire / Gratisography
  34. create view vw as select … from parent join child

    using ( parent_id ); Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only child cols?  Parent cols? 
  35. One table Select target columns Where clause => with check

    Many tables Can only load into one table Target must be key preserved Triggers Still fire on base tables insert to join views with instead of Insert into views Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  36. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  37. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  38. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  39. Multi-row inserts Load the results of a query Copyright ©

    2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  40. insert into <table> ( col1, col2, … ) select …

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  41. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL How many rows did it load? Ryan McGuire / Gratisography
  42. insert into <table> ( col1, col2, … ) select …

    sql%rowcount Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  43. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  44. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL REC_TYPE REC_DATA 0001 … 0011 … 0011 … 0011 … 0099 … 0099 … REC_TYPE => target table
  45. insert into t1 ( col1, col2, … ) select …

    from ext_tab where rec_type = … insert into t2 ( col1, col2, … ) select … from ext_tab where rec_type = … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  46. insert into t1 ( col1, col2, … ) select …

    from ext_tab where rec_type = … insert into t2 ( col1, col2, … ) select … from ext_tab where rec_type = … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  47. insert all into t1 ( … ) values ( …

    ) into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Every row in every table
  48. insert all when rec_type = … then into t1 (

    … ) values ( … ) when rec_type = … then into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only matching
  49. insert first when rec_type = … then into t1 (

    … ) values ( … ) when rec_type = … then into t2 ( … ) values ( … ) select … from ext_tab Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only one table, Top to bottom
  50. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Load parent & child at same time? Ryan McGuire / Gratisography
  51. insert all when rn = 1 then into parent (

    … ) values ( … ) when 1 = 1 then into child ( … ) values ( … ) select rownum rn, … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Order indeterminate => deferrable FKs Can't select seq.nextval!
  52. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  53. Performance Load data faster Copyright © 2021 Oracle and/or its

    affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  54. insert into <table> ( col1, col2, … ) values (

    val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL This is fast! Triggers? Blocking sessions?
  55. for … loop om asdf asdf asdf asdf end loop;

    insert into <table> ( col1, col2, … ) values ( val1, val2, … ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL But doing it 1,000x is slooooow
  56. forall … om asdf asdf asdf insert into <table> values

    rec_array ( i ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  57. insert into <table> ( col1, col2, … ) select …

    Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Check query performance Parallel? Direct path?
  58. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL What's direct path? Ryan McGuire / Gratisography
  59. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL High water mark Normal insert Direct-path Full Partial Empty Space
  60. DEMO Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon

    * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  61. Single row Record inserts invisible & virtual columns Multi-row Load

    a query Many tables with all/first Performance Use forall or insert … select parallel/ direct path for large loads Insert statements Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL
  62. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further reading Insert Live SQL Tutorial https://bit.ly/insert-tutorial Learn SQL for Free http://bit.ly/learn-sql-free-dfd-f Tuning Insert Live SQL Tutorial https://bit.ly/tuning-insert-tutorial
  63. Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon *

    blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Ryan McGuire / Gratisography See you soon! asktom.oracle.com #AskTOMOfficeHours