Search code examples
sqlsql-serversql-server-2017-express

How to Insert the same records in same table with different value under Id column? (Note : ID is primary key autoincrement column )

Suppose i have table level as

ID  name    abbr    countryid
1   None    NN  11
2   Middle  MD  33
3   Senior  SN  33
4   Junior  JN  22

No i want to insert the records of countryid 33 in same table with countryid 44 (Countryid 44 will be input parameter).But how to insert data under column ID? as Id is autoincrement?

INSERT INTO Master_LevelsGrades(Id, LevelName, LevelAbbr, CountryId)
(
select  ?? ,LevelName,LevelAbbr,@NewCountryId
 from Master_LevelsGrades where CountryId=33
)
Solution

  • Just leave it out:

    insert into Master_LevelsGrades (LevelName, LevelAbbr, CountryId)
    select  LevelName, LevelAbbr, @NewCountryId
    from Master_LevelsGrades
    where CountryId = 33;

    It will be set automagically.