oracle/pg插入大量数据30万条

单人影i

已于 2022-08-20 21:04:19 修改

阅读量1.1k

点赞数

分类专栏：后端文章标签：数据库

于 2022-08-18 18:01:47 首次发布

本文链接：https://blog.csdn.net/a185589690/article/details/126411127

版权

后端专栏收录该内容

42 篇文章 0 订阅

订阅专栏

oracle


begin
    for i in 1 .. 300000
        loop
            INSERT INTO ETS_ORDER ("SERIAL_NO", "REQUEST_DATE", "ACCEPT_TIME", "OPERATOR_ID", "AUDITOR_ID",
                                   "AUDIT_TIME",
                                   "TRADE_ACCO", "BUSIN_FLAG", "FUND_CODE", "SHARE_TYPE", "REQUEST_BALA",
                                   "REQUEST_SHARE", "OTHER_FUND_CODE", "EXCEED_FLAG", "DIVIDEND_METHOD",
                                   "CONFIRM_BALA",
                                   "CONFIRM_SHARE", "CONFIRM_NO", "CONFIRM_DATE", "AUDIT_FLAG",
                                   "CONFIRM_FLAG",
                                   "DEAL_FLAG", "DS_REQUEST_NO", "DEAL_TIME", "ORDER_BALA", "ORDER_SHARE",
                                   "REMARK",
                                   "IPADDRESS", "FUND_TYPE", "ORIGINAL_SERIALNO", "SPLIT_APPLY_NO",
                                   "FIX_BUSINFLAG",
                                   "OTHER_SHARE_TYPE", "TRADE_SOURCE", "OTHER_CONFIRM_FLAG",
                                   "OTHER_DS_REQUEST_NO",
                                   "SPECIAL_FLAG", "OTHER_CONFIRM_NO", "FISP_SERIAL_NO",
                                   "OTHER_CONFIRM_DATE",
                                   "EXPECTED_CONFIRM_DATE", "OUT_FARE", "IN_FARE")
            VALUES ((Select trunc(dbms_Random.Value(0, 10000000000000)) From dual), '20170517', '20220818153333',
                    '854887018150473728', NULL, NULL, '0682', '024', '000759', 'A',
                    '0', '17007361.38', NULL, '1', NULL, '17013044.23', '17007361.38', '3620000000187', '20170518',
                    '9', '1', '3',
                    '20220818153333', '20220818153333', '0', '17007361.38', NULL, NULL, '1', NULL, NULL, NULL, NULL,
                    'W', NULL,
                    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        end loop;
    commit;
end;

pg库

关键 generate_series


INSERT INTO tadata_04 (appsheetserialno, transactioncfmdate, currencytype, confirmedvol, confirmedamount,
                              fundcode, largeredemptionflag, transactiondate, transactiontime, returncode,
                              transactionaccountid, distributorcode, applicationvol, applicationamount, businesscode,
                              taaccountid, taserialno, businessfinishflag, discountrateofcommission, depositacct,
                              regioncode, downloaddate, charge, agencyfee, nav, branchcode, originalappsheetno,
                              originalsubsdate, otherfee1, individualorinstitution, redemptiondateinadvance, stampduty,
                              validperiod, ratefee, totalbackendload, originalserialno, specification,
                              dateofperiodicsubs, targetdistributorcode, targetbranchcode, targettransactionaccountid,
                              targetregioncode, transferdirection, defdividendmethod, dividendratio, interest,
                              volumebyinterest, interesttax, tradingprice, freezingdeadline, frozencause, tax,
                              targetnav, targetfundprice, cfmvoloftargetfund, minfee, otherfee2, originalappdate,
                              transferfee, fromtaflag, shareclass, detailflag, redemptioninadvanceflag, frozenmethod,
                              originalcfmdate, redemptionreason, codeoftargetfund, totaltransfee,
                              varietycodeofperiodicsubs, serialnoofperiodicsubs, rationtype, targettaaccountid,
                              targetregistrarcode, netno, customerno, targetsharetype, rationprotocolno,
                              begindateofperiodicsubs, enddateofperiodicsubs, senddayofperiodicsubs, broker,
                              salespromotion, acceptmethod, forceredemptiontype, alternationdate, takeincomeflag,
                              purposeofpesubs, frequencyofpesubs, periodsubtimeunit, batchnumofpesubs, capitalmode,
                              detailcapticalmode, backenloaddiscount, combinenum, refundamount, salepercent,
                              managerrealratio, changefee, recuperatefee, achievementpay, achievementcompen,
                              sharesadjustmentflag, generaltaserialno, undistributemonetaryincome,
                              undistributemonetaryincomeflag, breachfee, breachfeebacktofund, punishfee, tradingmethod,
                              changeagencyfee, recuperateagencyfee, errordetail, largebuyflag, raiseinterest,
                              feecalculator, shareregisterdate, totalfrozenvol, fundvolbalance, frozenbalance,
                              futuresubscribedate, transferdatethroughclearingagency, tacode, filedater, handletime)
VALUES (generate_series(1,300000), '20181225', '156', 83.27, 99.42, '000047', '0', '20181224', '000000', '0000', '2543', '362',
        83.27, 0.00, '124', '031203495622', '3622018122569388688', '1', 0.0000, '', '362', '20181225', 0.00, 0.00,
        1.1940, '362', '', '', 0.00, '1', '', 0.00, 0, 0.00000000, 0.00, '', '', '', '', '', '', '', '', '', 0.00, 0.00,
        0.00, 0.00, 0.0000, '', '', 0.00, 0.0000, 0.0000, 0.00, 0.00, 0.00, '', 0.00, '0', '0', '0', '', '', '', '', '',
        0.00, '', '', '', '', '', '', '', '', '', '', '', 0, '', '', '', '', '', '', '', 0, '', 0.00, '', '', 0.0000,
        '', 0.00, 0.00000, 0.0000, 0.00, 0.00, 0.00, 0.00, '', '', 0.00, '0', 0.00, 0.00, 0.00, '', 0.00, 0.00, '', '',
        0.00, '', '', 0.00, 0.00, 0.00, '', '', '03', '19999999', '2018-12-25 16:55:02');