Shulou(Shulou.com)05/31 Report--

Editor to share with you how to read data from the Oracle database to automatically generate INSERT statements, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle INSERT statement

Method 1

I guess people with a bit of SQL foundation can write INSERT statements. The following is the standard writing of SQL.

INSERT INTO employees (employee_id, name) VALUES (1, 'Zhangsan'); INSERT INTO employees VALUES (1,' Shangbo')

Method 2

In fact, Oracle also supports the following writing, which is exactly the same as the above statement.

INSERT INTO (SELECT employee_id, name FROM employees) VALUES (2, 'Lisi')

Method 3

In addition, like other databases, Oracle supports the following writing.

INSERT INTO employeesSELECT 3, 'Wangwu' FROM DUAL

Method 4

The following way of writing can achieve column conversion, such as we have the following table to store the original data, the original data may come from the file.

Create table sales_input_table (prod_id number (9), amt_mon number (9), amt_tue number (9), amt_wed number (9), amt_thu number (9), amt_fri number (9); insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0)

Let's use a SQL to insert the above data into the following table to achieve column wrapping.

CREATE TABLE sales (prod_id number (9), time_id date,amount number (9)) INSERT ALLINTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon) INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue) INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed) INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu) INTO sales (prod_id, time_id) Amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri) SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table

Method 5

The following way of writing can help us to pour the data from one table into multiple tables at one time, otherwise we have to write multiple SQL to achieve the same function.

INSERT ALLWHEN order_total 100000 AND order_total 200000 THENINTO large_ordersELSEINTO large_ordersSELECT order_id, order_total, sales_rep_id, customer_id FROM orders

Note that when order_total is greater than 200000, orders is inserted into large_orders and special_orders. This may not be the result you want. If you just want orders to be inserted into the special_orders table, you just need to replace ALL with FIRST, as follows.

INSERT FIRSTWHEN order_total 100000 AND order_total 200000 THENINTO large_ordersELSEINTO large_ordersSELECT order_id, order_total, sales_rep_id, customer_id FROM orders

Read data from Oracle database and generate INSERT statements automatically

Create a tabl

-Create tablecreate table TB_ACCIDENT_TYPE (ID NUMBER (20) not null, NAME VARCHAR2 (50), PATH VARCHAR2 (20), PARENTPATH VARCHAR2 (20), URL VARCHAR2 (20), TYPE VARCHAR2 (2), DESCR VARCHAR2 (50))

Display the data in the table

Select 'INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (' |'| | ID | |'|','| | NAME |'| |','| | PATH | |'| |','| PARENTPATH | |''| |''| | URL | |'| |','| | URL | |'| |','| |','| | TYPE | |'| | '|''| | DESCR | |''|) 'From tb_accident_type order by ID

Display the results

INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, and INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('5perpendiculary' region association', '5perpendicular recorder'); INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('568' recorder 'object strike', '1.1' minefield''area relation', '513' recorder''1''' INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (569 vehicle injury), INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (570 vehicle injury, 1.3 vehicle injury, 1.3 vehicle injury) INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES; INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (572, 572, and 1.5, respectively). INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('573, ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES, (' 573, ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (574, 574, 1, 5, 5, 5, 5, 5, 5, 5, 5, 4, 5, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 4, 5, 4, 6, 4, 6, 4, 6, 4, 6, 4, 6, 6, 6, 6, 6, 4, 6, 4, 6, 4, 6, 4, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 4, 4, 4, 4, 1, 5, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4 INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ("fire"); INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ("576 fall", "fall from height", "1.9 fall") INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (577, 578) VALUES, 1.10, INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES (578, 578, 1.11, 1.11) INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('580pm' recorder 'permeable', '1.12pm''1' 'recorder''); INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('581' Bo gun'', '1.13'] INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('582 ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR' gunpowder explosion', '1.14 minute' 1 'powder explosion') INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES ('583 minute' gas explosion', '1.15' last minute 'gas explosion') The above is all the contents of the article "how to read data from Oracle database and automatically generate INSERT statements". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.