All About Insert

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

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