SQL复制一个表的数据到另一个表

最近做一个项目，由于客户数据量大，为了不将数据彻底删除，于是将数据移动到历史表，原始表的数据删除。由于技术有限，想不到好的方法，于是写个存储过程

执行，为了防止执行过程中出现异常，执行不完整。用到hIbernate事务。下面是写的代码。sql存储过程写的很麻烦，希望能给点建议:

 

CREATE PROCEDURE [dbo].[savetohistoryDel] 
    --时间
    @fromtime varchar(20)
AS
BEGIN 
--插入作业到历史表
INSERT INTO inout_history_job
(
    jobid,jobtype,status,in_out_type,jobpos,invoicetype,isaccount,priority
    ,createtime,readtime,finishtime,shiftid,warehouseid,snumber,traycode
    ,route,op_man_id,taskid,scargo_wharea_id,scargo_space_id,scargo_alley_id
    ,tcargo_wharea_id,tcargo_space_id,tcargo_alley_id,jobcategory,isyfj
    ,isseq,indate,on_line_type,oldspacestatus,injobid,isfangfj,checkstatus
)
SELECT
    jobid,jobtype,status,in_out_type,jobpos,invoicetype,isaccount,priority
    ,createtime,readtime,finishtime,shiftid,warehouseid,snumber,traycode
    ,route,op_man_id,taskid,scargo_wharea_id,scargo_space_id,scargo_alley_id
    ,tcargo_wharea_id,tcargo_space_id,tcargo_alley_id,jobcategory,isyfj
    ,isseq,indate,on_line_type,oldspacestatus,injobid,isfangfj,checkstatus
FROM
    inout_job
    WHERE createtime < @fromtime
--插入作业详细到历史表
INSERT INTO inout_history_jobdetail
(
    jobdetailid,linestatus,jobid,inventoryid,productid,packid,lotid,punit
    ,jobnum,assignnum,customerid,invoiceid,invoicedetailid,boxcode,boxnumber
    ,productcode,isinvoice,injobdetailid,reserve1,reserve2,ownerId,lotatt1
    ,lotatt2,lotatt3,lotatt4,lotatt5,lotatt6,lotatt7,lotatt8,lotatt9,lotatt10
    ,lotatt11,lotatt12,isfj,checklot,op_man_id,scantime,isneed,ifboxcount,productstatus
    ,checkstatus,checktime,checkwho,reviewnum,mp
)
SELECT
    jobdel.jobdetailid,jobdel.linestatus,jobdel.jobid,jobdel.inventoryid,jobdel.productid,jobdel.packid,jobdel.lotid,jobdel.punit
    ,jobdel.jobnum,assignnum,jobdel.customerid,jobdel.invoiceid,jobdel.invoicedetailid,jobdel.boxcode,jobdel.boxnumber
    ,jobdel.productcode,jobdel.isinvoice,jobdel.injobdetailid,jobdel.reserve1,jobdel.reserve2,jobdel.ownerId,jobdel.lotatt1
    ,jobdel.lotatt2,jobdel.lotatt3,jobdel.lotatt4,jobdel.lotatt5,jobdel.lotatt6,jobdel.lotatt7,jobdel.lotatt8,jobdel.lotatt9,jobdel.lotatt10
    ,jobdel.lotatt11,jobdel.lotatt12,jobdel.isfj,jobdel.checklot,jobdel.op_man_id,jobdel.scantime,jobdel.isneed,jobdel.ifboxcount,jobdel.productstatus
    ,jobdel.checkstatus,jobdel.checktime,jobdel.checkwho,jobdel.reviewnum,jobdel.mp
FROM
    inout_jobdetail jobdel,inout_job job
    WHERE job.jobid=jobdel.jobid and job.createtime < @fromtime
--删除作业和作业详细
delete jobdel from inout_jobdetail jobdel,inout_job job
    WHERE job.jobid=jobdel.jobid and job.createtime < @fromtime
delete inout_job where createtime < @fromtime

--插入入库单到历史表
INSERT INTO inbound_history_invoice_header
(
    instockid,warehouseid,invoicedate,createtime,createmanid,instatus,intype,opmode,uploadflag
    ,auditmanid,auditdate,confirmanid,confirmdate,departid,shiftid,on_line_type,inpos,invoicesource,customerid
)
SELECT
   instockid,warehouseid,invoicedate,createtime,createmanid,instatus,intype,opmode,uploadflag
    ,auditmanid,auditdate,confirmanid,confirmdate,departid,shiftid,on_line_type,inpos,invoicesource,customerid
FROM
    inbound_invoice_header
    WHERE createtime < @fromtime
    
--插入入库单详细到历史表
INSERT INTO inbound_history_invoice_detail
(
    instockdetailid,instockid,productid,packid,eunit,boxnum,plattenum,invoicenum,puboxnum
    ,puplattenum,pucnum,lotid,reserve1,reserve2,providerid,linestatus,lotatt1,lotatt2,lotatt3,lotatt4,
    lotatt5,lotatt6,lotatt7,lotatt8,lotatt9,lotatt10,lotatt11,lotatt12,checklot,isdown
)
SELECT
    indel.instockdetailid,indel.instockid,indel.productid,indel.packid,indel.eunit,indel.boxnum,indel.plattenum,indel.invoicenum,indel.puboxnum
    ,indel.puplattenum,indel.pucnum,indel.lotid,indel.reserve1,indel.reserve2,indel.providerid,indel.linestatus,indel.lotatt1,indel.lotatt2,indel.lotatt3,indel.lotatt4,
    indel.lotatt5,indel.lotatt6,indel.lotatt7,indel.lotatt8,indel.lotatt9,indel.lotatt10,indel.lotatt11,indel.lotatt12,indel.checklot,indel.isdown
FROM
    inbound_invoice_detail indel,inbound_invoice_header inheader
    WHERE inheader.instockid=indel.instockid and inheader.createtime < @fromtime
--删除入库单详细和入库单
delete indel from inbound_invoice_detail indel,inbound_invoice_header inheader
    WHERE inheader.instockid=indel.instockid and inheader.createtime < @fromtime
delete inbound_invoice_header where createtime < @fromtime
--插入出库单到历史表
INSERT INTO outbound_history_invoice_header
(
   outstockid,outstatus,outtype,departid,warehouseid,warehousemanid,createtime,createmanid,formdate
  ,outpos,vehicleno,vehiclepos,auditmanid,auditdate,confirmanid,confirmdate,opmanid,shiftid,saleno
  ,on_line_type,isupload,ownerid,sendplatform,setposition,shipmentstarttime,shipmentendtime,customerid
  ,isfbup,accountyear
)
SELECT
   outstockid,outstatus,outtype,departid,warehouseid,warehousemanid,createtime,createmanid,formdate
  ,outpos,vehicleno,vehiclepos,auditmanid,auditdate,confirmanid,confirmdate,opmanid,shiftid,saleno
  ,on_line_type,isupload,ownerid,sendplatform,setposition,shipmentstarttime,shipmentendtime,customerid
  ,isfbup,accountyear
FROM
    outbound_invoice_header
    WHERE createtime < @fromtime
    
--插入出库单详细到历史表
INSERT INTO outbound_history_invoice_detail
(
   outstockdetailid,outstockid,productid,lotid,cargo_space_id,cargo_alley_id,wh_area_id,packid,pkgunit
  ,invoicenum,boxnum,assignnum,assignboxnum,sendnum,sendboxnum,customid,fbFlag,traycode,reserve1,reserve2
  ,confirmanid,confirmdate,linestatus,lotatt1,lotatt2,lotatt3,lotatt4,lotatt5,lotatt6,lotatt7,lotatt8
  ,lotatt9,lotatt10,lotatt11,lotatt12
)
SELECT
  outdel.outstockdetailid,outdel.outstockid,outdel.productid,outdel.lotid,outdel.cargo_space_id,outdel.cargo_alley_id,outdel.wh_area_id,outdel.packid,outdel.pkgunit
  ,outdel.invoicenum,boxnum,outdel.assignnum,outdel.assignboxnum,outdel.sendnum,outdel.sendboxnum,outdel.customid,outdel.fbFlag,outdel.traycode,outdel.reserve1,outdel.reserve2
  ,outdel.confirmanid,outdel.confirmdate,outdel.linestatus,outdel.lotatt1,outdel.lotatt2,outdel.lotatt3,outdel.lotatt4,outdel.lotatt5,outdel.lotatt6,outdel.lotatt7,outdel.lotatt8
  ,outdel.lotatt9,outdel.lotatt10,outdel.lotatt11,outdel.lotatt12
FROM
    outbound_invoice_detail outdel,outbound_invoice_header outheader
    WHERE outheader.outstockid=outdel.outstockid and outheader.createtime < @fromtime
--删除出库单详细和出库单
delete outdel from outbound_invoice_detail outdel,outbound_invoice_header outheader
   WHERE outheader.outstockid=outdel.outstockid and outheader.createtime < @fromtime
delete outbound_invoice_header where createtime < @fromtime
END

    public void saveHistoryTabAndDelete(String time) throws Exception {
          Session session = null;
          Transaction tx = null;
          try {
            session = m_dao.getSession();
            tx = session.beginTransaction();
            SQLQuery query = session.createSQLQuery("{call savetohistoryDel(?)}");
            query.setString(0, time);
            query.executeUpdate();
            tx.commit();
        } catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally{
            m_dao.closeSession(session);
        }        
    }