Shulou(Shulou.com)06/02 Report--

This article shows you how to use row_number () over () in Oracle to solve the problem of repeating keys when inserting data. The content is concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Import destination table

As you can see in the figure above, the primary key in our destination table has three orgcode,saleno and serialno

Source table

The source table is still the table in our SQL database

From the two data tables, we can see that some columns need to correspond to ourselves, but there is no serialno in the source table. Through table analysis, we can see that if there are two ZfCode in the same SaleNo in the data of the source table, if we set Serialno to a default value, it will definitely become an insert repeat key.

Let's execute the default insert statement, and the problem of duplicate keys pops up directly.

We took a look at the data in SaleNo 2019040100015486, there are two pieces of data, press the primary key orgcode,Saleno,Serialno of the table we want to import, so the insertion must be a duplicate key.

To solve the above problem, we use the function of row_number () over ()

Modify the query data by grouping and sorting the Saleno

You can see that the two pieces of data under the same SaleNo are sorted automatically.

Complete insertion code

Insert into tSalSalePay201904 (Orgcode,Saleno,Trantype,Serialno,Zfcode,Zfname, Zfno,Paysstotal,Zftotal,Sstotal,Jzdate) select'0' as orgcode, "SaleNo", "TranType", row_number () over (partition by "SaleNo" order by "SaleNo") as Serialno, "ZfCode", "ZfName", "ZfNo", "ZfTotal", "ZfTotal", "ZfTotal" "JzDate" from tJkSalePay201904@Odbc_Sql src where not exists (select * from tSalSalePay201904 dst where dst.Orgcode='0' and dst.Saleno=src. "SaleNo")

Basically, the function row_number () over () is mainly used in sql of all kinds of data statistics. If it feels better than group by, you can group multi-column data in a query, especially in multi-table association queries, row_number () over () is still very convenient.

The above is how to use row_number () over () to solve the repetition key when inserting data in Oracle. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are 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.