How to insert value in an auto incremented column in SQL
Sometimes we have situation to insert value in an auto incremented column. Let's discuss this with a simple example.
Example
1) Here is my table structure.
CREATE TABLE Student(
Id INT IDENTITY(1,1),
Name VARCHAR(10)
)
2) And I have some entries in my table.
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAG0AAABzCAIAAAAdRhvqAAAGjElEQVR4nO2dO5LqOhCGtSEoYkd3C1TBFpQTcCedHeCAOrOJyeaU1kBI3SLzYnwDyXq/rG6BGesv1RRjjJA/t2SrW23I5+fnP01gEc5xrCaUyqu2EC6D4+N+Qy/6SXptJfUKb17j2Dguo7yU489ptz2x+JacSsjm4yddQ+OY5EiI/FTjWM5xe/o4kOO1ceQ2xVXEkelVbU/sfvs6yBrJ8cq3749bQgjZnS8f4sX3w/h2bXx4Q47fH9vJmq77Qo7329dB8vLZuBxG9Rfi2yd8AFteAEe99aX9mt1vj/vlaBmmbuOOwWp8dRWa5G/ieGPnze6w321P7H45EtPKYhz3X0jHuJR+zc6b4n79uN8e98tRjLCXo6TDx4oQR31UATBdAketD243EHvUzwQ7b0hOv5b03/g68/RK6pXGsXFcUrE5ftaRRPDySupJcayqcRwXUkk9CY61jZ8QgvJ3sUVxHOuIV/4fWPVaCJfNsdIYzK0JyJEQ8mpcQT2PY7PHZo9p5XDUpqu17PHPgRz+FNvj0Hea04YyDDBD33X9gMTxm7s8CQbHgD3+/VfMrtMcg/aoH7N1/DNxVOL4PvaojplRFItcLMfo+JjFcYY9Dn3XUcp7e9f3VPV2RknXD4ya48DQd3w3c8sgPkFIiulvsUeFpesHvmV6wbEKkI61qh2mN9SZ6AeBfYX2aG2ZXgh+EqOOXlGzyeZftn6LPaY4coKMyn+mDxjWZ3GkbFSWvBSOr7VHc5jTejdH6h9hpxoySK7GHg0jdEbUCEexc2KUzOEIKouZzyDdDwX0PI5AiDF7TCrr1gWk9dhjXdkcUZ3tQq+3x/oyOAKc6gmtyx5rl18cVLD7dQ1hVf42/XqseZ1BaSu8kkpqHHHUOOIowVEtED5cKnFUrsDUdCNQCaPWR+WMuNgZPl8xjuy8mfBp6wRROWpOgLQ/IFKJHUqg1PbiVFZuv2bnjViYXq1fJ486WInxSdPBsDCOtexRU7k9etjZPptpgq3iDZR6QgsiFNH1rO+kbx2PY2TFMRZHGQgo4uj4yUKxAe/XKMetLyaRQzGHIwRiJscciGP8em3wC8cGLD+j1wXp811COQIh5nDMhJjgyA+amQjc2IDR0wOhBXyO1z1kBUAWR+empZTjOPRd100HHooNGBwDoQV0jnp2GZGJZKgc9ThyclxPDLK6YYdiA8za8qx+DS9tPtM4zlDjiCObI2pAQWh1HAGBg4TWxbEVSCGIXc+r1dnj2K4zpWoccdQ44ijBUU4Ny5yPORyZvpo41dbY24wir+HB9FOopHnQMweCCJTbJb0eLM6RUUIpxQRZYX6tPZ+lVr9OtzpaydB3lI0MEyQ+R/AzMNIcM5a9xirhGE0/bm+NGMrD4/p8lL9N/e+tpJgjf2ZGvfFRth4yPgqMul/cyj/wxL/cLdPYwk+qpxIAx6lfLzzOZXoxI/kHcifmbJl2MpzBeiVgjouPu6ozYAYYDI5O5kzgxDFKCLdHFI5vvw7AeNv9KmeL8qgHTkYZx4d2/1hmjDn2KO8fk9fGQCVOyhXvnR4E7lfZW9zrDHK/Li5tPtM4zlDjiCObI2pAQWh1HAGBg4TWxbEVSCGIXc+r1dnj2K4zpWoccdQ44iiL49eh4ryQK+d5GolK0OMKc5TB8ee0qzm/Hse8jPvnxxXmKMnx+2O7OR4KnWZ5HG0HTaSt0Uqw4wpzlOJ43ZPDpdj5mMWRUcvXF2lr8D1PXMGffJB+AlKR4hxFmLAmR+HKB3L0xRVSyQeanzwzeBBvW5CjxFeP46w1xGGO4biC90U8AoHNUT5sD+TKjXJ0gybYcQWbY8YTkAoo5uch1b7vgdhjLK7g4Zh6AlIWN0/b3p1jNK7g9OtgmkJmI8Jta/MZBDWOOGoccWRzRA0oCK2OIyBwkNC6OLYCKQSx63m1Onsc23WmVI0jjhpHHMU5yt+kI8Dfmgwj0P2BkPXh0bhC5oSv2rwQ9IThbI44z1WIxRVCzsfkbtmKcsT4GeQEx5k5AeE3o3GFF9uj9jOytfw9mvsFM66QzDxw3D9zExTmcFR5SNXW2SunF2SdvRNXSGYegBMU5nDUyqLX2btxhWlLMPNAzw8pSlBw2/b+HN24wlRNMPPA/LKCBAW3bUGOWr6C9ovSqBy1zpxeURGpxBNXiGceqGBhYYLCDI6PN85XoH088yB8nanbr4tLm880jjPUOOLI5ogaUBBaHUdA4CChdXFspbiM4/g/Gmmdjb5Sv5sAAAAASUVORK5CYII=)
3) Now if I delete some entries and insert new record then new record will come with Id = 6. I have deleted record with Id = 3.
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAHgAAAByCAIAAAAXnYLjAAAG/ElEQVR4nO2dPZKjPBCGdSG7HBP5Cq6yr6Dcgb9J5wYmcO1cYrLZ0hkcurbIOAxfIBASILXQT8v29FvUlscLbfy4aaBfBGy/33fZlCR41jXE0X6/Z/JrNI978unz81MxKhuk7CS/AoEm0AQ6ltHPebc9C/c7PkHY5uMHjlB8egPQjKmlCHRG0Nvzx5GdbgQaBC2zUioItNBDbc/icf86qojsdJPvH05bxhjbXa4f/Yvvxvh0rQS9I+jvj+2Qj7dDIOjH/euogC5tJaqU6y/6Tx/4Zt4aSoPWv15o6RCPe/O4niaprW8ls5TXfgBdGZP6bUDfxWWzOx5227N4XE/MzFMX6MNXHrJPBlorHeKyCS4dzePePK6nvspfTwqfLEc20Hrhygy9OGhtM99uYjJa/6nEZcN8Sof6ed55Z4gepOxEoAn0G4P+zCPFqHiQsupBZ1XXdU8SpKzYfr/Pve0wxpL8+9JTD7rLIxn8X7TyrSGODNDJf0NZm2Q+RoJmjJVmFSUk0JTRlNFIAkFrrYNcGf3nyI5/gjO6rSutBcdFCiptXVV1myKSkgP0t+yRsxSgLRn997++0wGDtma0DmUCKJgXLujXyegRiuBJcvp1QTtrtBfoFRnd1lXFuSwoVV3zsaAIzqq6FdwsNW1dydnMd9p+CcZSQH+LjB65VXUr3xleSO496Vm+jzMM/zH+VHXb/y7RjDFBI2X05J3hRQ9YcdZ/mxHrFH26fSsi6LwZDYGWiAVXfwwLGPk7Ac1FN24LLwO6bEabpVYrIJL5cpUfIiRC/Tsy2kjjWVV3gO5nTlCpQdBR09OcGSY67IsQEuhIyq6MBpXsCC1KvySjy8sAncnCKZzRz6ERdIRHA4gyutNB555+uY9llI4cShX8fUpHl3NnmGRF44MUFIFGEoFGkgv0OEbheM0EemwNQyduliCCTxZV3YkMzfsYWUGLy2bgq11HnBS01rGBmzeOIFP3ivNpT+4J5FU6xGXTj67JVjpALNYgxpJmN+jVQOfKaE3hGb0Ad9qBG5odo8XF+YKb1btfVS3qSrk1yQSDdgx6SAVaeU/uFXUsbrRFbXbU4seMnf4lG8wHoZ8A0DGUPUH7UO7cRx0GYLsdNek7L7akl3rZSeQCHUnZB7Qn5Q44vGvrqqqFyWhuRxnFxOJmFQB9O8RcOuMFenZs5l5Rx/+2dVVVAxmbHWWAtrhZ+KD1gb5MjelNClq/vgLc+QCFXt80bHaUmLzzNKUjfqIzQyUCjSQCjaQGx8pKsqLxQQqqwbGykqxofJCCatCsLJpYqqRbFGW0VEM7QxwRaCQRaCS5QKuz8LBmtD9onyvrgSCCP8H1dS7ZQY+37Qq/ctcLtN+Fse4ggjPO+TOT9ikd2m3L0oMeTT5wRZ1BuOjEM5P2AB1xYycYtOCMC59OmSuI5Gw0/pd9KXg8VjYBoOWNoLLV6L4vHAm656w7LaAvZRuPtQLdOnmWjlyXG/j3fu2gjd4y0FwGx2MFQfSRD+hMlxuYgIIb/yqf+z9mntb4wmM8VgBCP1lBI1xAoxST0dYLaLpF0NB4LC9oIXJltDqODktnFNCzQ3C5nVhKBzwey5fbanmVjuCJzgyVCDSSCDSSGrKycNSQlYWjhqwstImlSrpFUUZLNbQzxBGBRhKBRhIM+uuY7xRc7w8D578AaIeV5XluXfgU/Oe8y9jrWHG/Ejdol5XluEUN+GY6uUF/f2w3p2NgjxQGvea7OUE7rawXyOjbgR2vwc1oL9CehWOVlaUFVkNbFu8fCMyWVA7QvfmdEfTY44wZ/jazslRFklHnftWkPW2bbTVMYP2XQSu+GUGbsEKHVsysrOEdY0iL/jGat+iazZehl2yg1a12o3r/GKDnVtYQRnDGJh773MFyzAau9Bo5a7SZ2slBa/UCvlbJEWTBytItFmEBPY4vssy2CiSkkqA77UA6qZXF69lebjGj5zvDkqBjJjozVCLQSCLQSGrIysJRQ1YWjhqystAmlirpFkUZLdXQzhBHBBpJBBpJDtDqUc4s+GnOMGjh2wB2BdH6d74DCOYRAs65VxoXDtCZb/Wj3esH7Co5g+g3lvEbEjNRSdDm482zgF7z5B5rEE9L0K3CoOOa0SBo2FYxV9T+dc3fa8mUmo7H8vCx0j5Syw56HNCZc7DQ8PCwqBptQuvcplSAj2UbwjUfGuNcf/ioI9O9SbVUjPIMRzlGpkzGY/n4WOAQLjWDx3ZZErR+fUCEZ2hGXDalZuOxtAUsPlbiR2pZQWujsq6nXId3xvPYgm9rPC5pzeil8ViAj5X4kVqujMYYlZXiOFrba81u0T1zrcxK7vKxpos4Td4WunOAV+kInujMUIlAI4lAI6khKwtHDVlZOGrIysKZuq77H1V8bC4ZjMFQAAAAAElFTkSuQmCC)
4) Now if I want to insert new record with Id = 3 it will give error as Id column is auto incremented. To do this we have to set IDENTITY_INSERT to ON and after insert we can set it OFF to disallow manual insert.
Syntax
SET IDENTITY_INSERT TableName ON
INSERT INTO TableName (Col1, Col2...) VALUES (Val1, Val2...)
SET IDENTITY_INSERT TableName OFF
SET IDENTITY_INSERT Student ON
INSERT INTO Student (Id, Name) VALUES (3,'Aneesh')
SET IDENTITY_INSERT Student OFF
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAHsAAACHCAIAAAC59bFKAAAIDklEQVR4nO2dTY7qOhCFvSEQ44zuFpBgC5kz4PW0d0AG6PYmetZPXgND9NSzLCZv4MTxv4vYZQeoo+iK5oIxnysVUocK7Pd++/z8/PPnz4CmLIOjzrCMxFtgKvHf+y37pi5n3UHqbvItEHEiTsSxiP+cdtsTD98DGYRtPn7iI1TfXok4Y/JZRLwE8e3p48COVyIOJy7iVGgRca4OtT3x++3rIEdkx6u4f3/cMsbY7nz5GG98/2qvrmSnlyb+/bGdIvS6X0j8fvs6SLKu/Uame/XG+OoTaOT9YzXE1fe5NKvw++33fjkawa7uN9ZOoKyEKsQwfz3iN37e7A773fbE75cj0yM3RHz/hYN4rcSVrMLPm8VZ5fd++71fjuOR4HKUHEWm8hFXcxoy/fUQVzLAdpMS4+qa8fOGQbKKXKe3OHIWH6TuRsSJ+HsS/8SRhFV9kLoyiaNqGIaVDFJXGnHs3YoxluXfp95M4gOOxOD/JQtvhmXkJp59VeXgjLFE4oyx2tCSVJo4xTjFeGnBiStlCqwY/3tgh7+LY7zvGqUA2PIcePquabo+x0hSEOLfonjPchD3xPi//4xVlThxb4yrdAxSi8FVIv48MT7T4W2WKH8B4sE8DiL+QIz3XdO0rcg1Tde1c67hLWu6nrd6Fuq7RjxMv6cfn8FYDvqvFeMzwKbrxT3TDbEAI3JrD5gfMP3HvGZdPy5QMuwqxAvFuHHPdGMkLYGrizTzNdcg34G4BnHcGI8RF6x5K/+YnqBFtEG85cO8dzwf8boxrqdjJbcI+O4jwTRCJuZvFuNaYFuZP0B8fHCGbA4nnrSt5pwz06fGBJUmnog7FONRZfuAl6R3i/H6chNHcpsqx/g65CCeYCdFRDE+OIljb29uubmzCoZyDf6CWWXAPHLmmvHzioiXFhEvLRDxub3jcEEiPpeqY6eEnkF4azxVVkIQXIUUxYnz82YCrXzPOitxpUwUrxgFBjGNtrY1K4Ir0GNZhZ83Y6sSWlaJ8vEOoj1TL0E9LXGsGFe0PMYdlM3631RYmd24tnUYb6NR13S8a6SflE0PEA/0i+QiLm2y6Ix9T9eqsz7nzPkyswXhcuwAKIGCEk/BDSQOwT2EP6topP3OmVEHd5bIXbX1LAIRT8QNIQ7EPUQ+HfZd03Rch2U7Z1qe8RhvNYlf9ynfDQIRtz7aRWfsU981TTMh8jlnGnGP8VaRuNprzWRbdVbi6vdGokeqyMFA3Vl8zhk37llfVknf6JxTioiXFhEvLTdxPNct14yfVw7iCbZaRER8qOK60WYSR1pbinEhN/Hsq0p5XIqIlxYRLy0QcXmiv6w4DiHO1V4QwIy94m3mgnadStZ8oZ2FJa14JcsuLQVn7BNvWdu2ORFVrasoV6nDyirx9xccpO+alg88J/KaxBOu2gUlDmhFCA0igOuORGfkq7laaNcP5yrv/LdzkARBiYurfOHlcfk+U/L4CFz1gozeNYfjbN8zZTax/I5BkvRoVlm5s6xX2QO9a/JB3LpnepBma6iDgOEG5v8q356Y10q33waVuNXJ6Vli3jImYrww8af/hpD23/ZLWffMLpJn2YBog/OHfh5fFuCQGJefx6PvyDOI1VYscoMDlv1S5j32kbNaVlm80TmnFBEvLSJeWm7i5LrhyUE8wVaLiIgP5LpV2UziSGtLMS7kJp59VSmPSxHx0iLipfUA8a8D4lm+EOQKYJFBsrtuuQUm/nPaYdZVhgF6lZ7wIPldt9wCEv/+2G6Oh4WlWhhxs9gXnrF/kNyuW27BiF/37HBZXBwHEeetUYsOz9gth+vmblyLX1sSTRDio4WPSXw0uhKJu1y3WOOa4g1ltNai8484EgI0HvGHOkD8xP2um/NG2J9bAhOkKHF5YeYkUyJI3DYfc7tuJnHAtSUfRwkULI/rwY6Sx8FvNrqjzH/KA7GDeOzakhB4i/QyxIOum5VVvC1uwEkk6AHiKRudc0oR8dIi4qXlJk6uG54cxBNstYiI+ECuW5XNJI60thTjQm7i2VeV8rgUES8tIl5aEOLyl83Z4h83B1RrgYWsBNcNePq+grP8pN/sABPPc9WmkOvmK45HH5ZVAOI/p13aZcnixB95k0HiQdftaWL857RLK46DiANzykOuW7RrzSolYjS3OecP7KBF67yaS60pnVeW6xbtWivS3Oac/0t0Xtmu23SPt2tN7UJEa25zzv8liNuu2zSMt2tNfzGk5jbn/IG9bpcjzqdDJZXEv5UVGMThuoW71mYjH7G5zTn/V+11a7tw15r/yLmWrLJ4o3NOKSJeWkS8tNzEyXXDk4N4gq0WEREfyHWrspnEkdaWYlzITTz7qlIelyLipUXESwtG/Lofz8HRftcNfonJ0CBK9RDacmGPsOC0/nFHJe5zirL44gZDiM8pUEdLWcFBlO/hA9uKDK2C+OxIoGUVSB+nPmPQ235S4kq1Fot43PixZuwbRnvjLv/M7HIDWG7xxriZ+PTTcYnEd4f9DjOPK12AaXlcpzeE/bMFlpuvMc5uL4rNPxbjyD6nEpxJPuesQHeP0eUGsdyijXHyAYA9FZTH5dESy3VTvu6Q4HPqI7r9M6vLTXmCx3IDNMb11qUTY/MHfkMI7yqq8htCy2Nce6Y3xl1dbhHLDdAYp+0NoCsdv8jnceUQZ/3CsmWw6dk+ZLmZTwk61H3scg4w4skbnXNKEfHSIuKl5SZOrhueHMQTbLWIiPhArlv5TXCfiVdd/jcSES+t/wF97NoKgLB3BgAAAABJRU5ErkJggg==)
0 Comment(s)