将一张表的数据导入另外一张表，但是另外一张表多了一列主键

各位大神们好， 我想把A_Applicant1表内数据插入到HA_Applicant,但是我查看了 HA_Applicant多了一列Update_ID,是主键，不为空，没有identity标识，且是设置为自增长+1的。这边应该是直接插入的时候+的1.
我在下面语句里加1了，我的逻辑是这样的，从HA_Applicant里取出最大的（Update_ID）, 但是我这边想对应Update_ID是、
Select语句中的,Application_Number 所对应的Update_Id,不是select语句中的Application_Number就不取出来，但是我这么写， HA_Applicant历史纪录都出来匹配了。如何解决这个从HA_Applicant中取出当前Select出来Application_Number对应的最大的Update_ID，然后自动加1，然后insert into HA_Applicant(select出的所有Application_Number都要insert进去，没有select的就不要插入)
INSERT INTO [dbo].[HA_Applicant] 
           ([Organisation]
           ,[Country_Code]
           ,[Application_Number]
           ,[Application_Type]
           ,[Sequence_Number]
   ,[Update_Id]
           ,[Id_Number1]
           ,[Id_Number2]
           ,[Id_Number3]
           ,[Surname]
           ,[First_Name]
           ,[Middle_Name]
           ,[Sex]
           ,[Date_Of_Birth]
           ,[Home_Address1]
           ,[Home_Address2]
           ,[Home_Address3]
           ,[Home_Address4]
           ,[Home_Address5]
           ,[Home_Address6]
           ,[Home_Postcode]
           ,[Home_Phone_Number]
           ,[Mobile_Phone_Number]
           ,[Company_Name]
           ,[Company_Address1]
           ,[Company_Address2]
           ,[Company_Address3]
           ,[Company_Address4]
           ,[Company_Address5]
           ,[Company_Address6]
           ,[Company_Postcode]
           ,[Company_Phone_Number]
           ,[User_Field1]
           ,[User_Field2]
           ,[User_Field3]
           ,[User_Field4]
           ,[User_Field5]
           ,[User_Field6]
           ,[User_Field7]
           ,[User_Field8]
           ,[User_Field9]
           ,[User_Field10]
           ,[User_Field11]
           ,[User_Field12]
           ,[Full_Home_Address]
           ,[Full_Company_Address]
           ,[Age_Of_Applicant]
           ,[Full_Name]
           ,[User_Field13]
           ,[User_Field14]
           ,[User_Field15]
           ,[User_Field16]
           ,[User_Field17]
           ,[User_Field18]
           ,[User_Field19]
           ,[User_Field20]
           ,[Income_Increase_Percentage]
           ,[AppKey])
    select
            Organisation
   ,Country_Code
           ,Application_Number
           ,Application_Type
           ,cast(Sequence_Number as smallint)
   ,(SELECT TOP 1 ha.Update_Id+1,ha.Application_Number
FROM HA_Applicant(NOLOCK) ha
join A_Applicant a on a.Application_Number=ha.Application_Number
and ha.Sequence_Number = a.Sequence_Number
--where ha.Application_Number = @Application_Number
ORDER BY Update_Id DESC)
           ,Id_Number1
           ,Id_Number2
           ,Id_Number3
           ,Surname
           ,First_Name
           ,Middle_Name
           ,Sex
           ,convert(varchar(10),cast(Date_Of_Birth as datetime),120)
           ,Home_Address1
           ,Home_Address2
           ,Home_Address3
           ,Home_Address4
           ,Home_Address5
           ,Home_Address6
           ,Home_Postcode
           ,Home_Phone_Number
           ,Mobile_Phone_Number
           ,Company_Name
           ,Company_Address1
           ,Company_Address2
           ,Company_Address3
           ,Company_Address4
           ,Company_Address5
           ,Company_Address6
           ,Company_Postcode
           ,Company_Phone_Number
           ,User_Field1
           ,User_Field2
           ,User_Field3
           ,User_Field4
           ,User_Field5
           ,User_Field6
           ,User_Field7
           ,User_Field8
           ,User_Field9
           ,cast(User_Field10 as bigint)
           ,cast(User_Field11 as bigint)
           ,convert(varchar(10),cast(User_Field12 as datetime),120)
           ,Full_Home_Address
           ,Full_Company_Address
           ,cast(Age_Of_Applicant as smallint)
           ,Full_Name
           ,User_Field13
           ,User_Field14
           ,User_Field15
           ,User_Field16
           ,User_Field17
           ,User_Field18
           ,User_Field19
           ,User_Field20
   ,cast(cast(Income_Increase_Percentage as real) as decimal(18,2))
           ,AppKey
from A_Applicant1(nolock) 

解决方案 »

  1.   

    "Update_ID,是主键"
    --> 主键字段是不可重复的, 但按LZ的逻辑,可能会产生重复的Update_ID喔.
      

  2.   

    代码太长,给你举个例子吧:declare @a table
    (
    id int
    , name varchar(10)
    )declare @b table
    (
    name varchar(10)
    )insert into @a
    select 1, 'asd'insert into @b
    select 'a' union all
    select 'b' union all
    select 'c'declare @count int = (select ISNULL(COUNT(1), 0) from @a)insert into @a
    select ROW_NUMBER()over(order by getdate()) + @count, name from @bselect * from @a
      

  3.   

    declare @count int = (select ISNULL(COUNT(1), 0) +1  from @a)