将一个表的数据导入到另一个表的sql

ALTER PROCEDURE [dbo].[usp_ea_Copy]
    (
        @eaId            int,
        @createdBy        varchar(50),
        @newEaId        int  output
    )
AS

declare @count as int
declare @newId as int

select @count=count(*) from  Ea where id=@eaId;

if @count<1     
begin
    RAISERROR ('eaid is not existed ', 16, 1)
    return -1
end    

--unit_titlecode,ReceivedTime,ReceivedletterNumber(申报文号、接收时间、接收文号不copy)
--createdtime设置为GetDate(),Status为未提交状态，即'0',createdBy设置为当前用户id

set transaction isolation level serializable

begin transaction

insert into ea(unit_code,unit_title, comptroller, group_leader, abroad_mission, arrange_unit,
            foreign_unit, city_start, send_num, send_time, stay_days, mission_type,AbroadProjectNo, 
            cost_source,DoForeignEx, RouteLine, Cost_add, Memo, Memo2, Principle, phone, CellPhone,fax, Email,
            Invitation, ScanFile, ScanFile2, createdtime, CreatedBy,status )
select        unit_code,unit_title, comptroller, group_leader, abroad_mission, arrange_unit,
            foreign_unit, city_start, send_num, send_time, stay_days, mission_type,AbroadProjectNo, 
            cost_source,DoForeignEx, RouteLine, Cost_add, Memo, Memo2, Principle, phone, CellPhone,fax, Email,
            Invitation, ScanFile, ScanFile2, GetDate(), @createdBy ,'0'  from Ea where id=@eaId

select        @newEaId=max(id) from Ea  where unit_titlecode is null
            
commit transaction

set transaction isolation level read committed 


insert into EaUnit(eaid,unit_code,send_num,memo,sequence)
select        @newEaId,unit_code,send_num,memo,sequence from EaUnit where eaid=@eaId


insert into EaRoute(eaid, city, country_code, stay_days, mission, is_trans, Sequence)
select        @newEaId, city, country_code, stay_days, mission, is_trans, Sequence from EaRoute where eaid=@eaId

insert into EaPerson(eaid, xmbm, name, idcode, unit_code, subUnitCode, subUnitName, Job, kshlx, wychj, tlchj, flchj,
            kh, position, professionLevel, HealthStatus, ExaminationScan, Sequence, PositionLevel,feeGrade)
select        @newEaId,xmbm, name, idcode, unit_code, subUnitCode, subUnitName, Job, kshlx, wychj, tlchj, flchj,
            kh, position, professionLevel, HealthStatus, ExaminationScan, Sequence, PositionLevel,feeGrade
             from EaPerson where eaid=@eaId
                     

insert into EaHighGradePerson(eaid,Name, Xmbm, PositionName, PositionLevel, Mission, LastAbroadTime, LastAbroadCountrys,
            LastAbroadDays, LastAbroadMission, Sequence)
select        @newEaId,Name, Xmbm, PositionName, PositionLevel, Mission, LastAbroadTime, 
            LastAbroadCountrys, LastAbroadDays, LastAbroadMission, Sequence from EaHighGradePerson where eaid=@eaId
                            

insert into EaRetiredPerson(EaId, xmbm, Name, Age, Helth, Job, Sequence)
select        @newEaId, xmbm, Name, Age, Helth, Job, Sequence from EaRetiredPerson where eaid=@eaId


RETURN