none
How to write query insert data to GeneralTypes increased by one based on max number on TypeId ? RRS feed

  • Question

  • I work on sql server 2012 I have table name GeneralTypes

    as follwoing

    create table GeneralTypes
    
    (
    
    TypeId  int,
    
    TypeName nvarchar(50)
    
    )


    TypeId is primary key but not identity

    I have temp table as #tempGeneral

    create table #tempGeneral
    
    (
    
    TypeId int,
    
    TypeName nvarchar(50)
    
    )


    I need to insert data on GeneralTypes Based on temp table #tempGeneral

    so that i will check max value exist on table GeneralTypes

    then i will increase it by one when insert 

    and if temp table #tempGeneral Have values on TypeName not exist on table 

    then get max and increase one 

    so that what i need is to do write query insert data to table Generaltypes from #tempGeneral

    #tempGeneral have following :

    Mercedes

    Ibm

    Mazda

    Toyota

    after insert data on table GeneralTypes data expected will be

    typeid  typename

    1        Mercedes

    2       Ibm

    3       Mazda

    4       Toyota

    suppose i add new values to  #tempGeneral 

    Mercedes

    Ibm

    Mazda

    Toyota

    oppel

    Honda

    Hundai

    then i will check  TypeName on table GeneralTypes

    if typename on #tempGeneral exist on table GeneralTypes then i will not insert data

    if #tempGeneral have Typename not exist on table #tempGeneral then add typename not exist on table GeneralTypes

    so that I will add these typename on second time

    typeid  typename

    5        oppel

    6       Honda

    7       Hundai

    so that i need to write one  query insert data exist on #tempgeneral on table GeneralTypes increased by one

    and if not exist get max value on type id on table GeneralTypes and added increased by one 


    Thursday, December 12, 2019 2:27 AM

Answers

  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 8:54 AM

All replies

  • declare @Maxid bigint
    select @Maxid =  max(typeid) from GeneralTypes
    Select @MaxID+ROW_NUMBER() Over (Order by typename) as typeid,typename from #Tempgeneral A where not exists ( Select 1 from GeneralTypes B where A.typename=B.typename)
    


    Hope it Helps!!

    Thursday, December 12, 2019 3:18 AM
  • Check this query too:

    insert into GeneralTypes
    select ROW_NUMBER() over(order by TypeId) + (select MAX(TypeId) from GeneralTypes) as TypeId, TypeName
    from #tempGeneral
    where TypeName not in (select TypeName from GeneralTypes)


    • Edited by Viorel_MVP Thursday, December 12, 2019 6:02 AM
    Thursday, December 12, 2019 6:00 AM
  • Hi engahmedbarbary,

    I use the same script to insert your data and satisfy your requirement. Please check.

    drop table GeneralTypes
    drop table #tempGeneral
    go
    create table GeneralTypes
    (TypeId  int,
    TypeName nvarchar(50))
    create table #tempGeneral
    (TypeId int,
    TypeName nvarchar(50))
    insert into #tempGeneral(TypeName) values ('Mercedes'),('Ibm'),('Mazda'),('Toyota')
    insert into GeneralTypes
    select row_number()over(order by (select 1))+(select count(*) from GeneralTypes) TypeId,TypeName 
    from #tempGeneral a
    where not exists (select 1 from GeneralTypes where a.TypeName=TypeName)
    select * from GeneralTypes
    
    /*
    TypeId      TypeName
    ----------- --------------------------------------------------
    1           Mercedes
    2           Ibm
    3           Mazda
    4           Toyota
    */
    insert into #tempGeneral(TypeName) values ('oppel'),('Honda'),('Hundai')
    insert into GeneralTypes
    select row_number()over(order by (select 1))+(select count(*) from GeneralTypes) TypeId,TypeName 
    from #tempGeneral a
    where not exists (select 1 from GeneralTypes where a.TypeName=TypeName)
    select * from GeneralTypes
    /*
    TypeId      TypeName
    ----------- --------------------------------------------------
    1           Mercedes
    2           Ibm
    3           Mazda
    4           Toyota
    5           oppel
    6           Honda
    7           Hundai
    */
    

    Bets Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 12, 2019 6:55 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 8:54 AM