title: SQL Server: insert next available int
body: <p>I'm dealing with a table in SQL Server that has a <code>serial_no</code> column, which is defined as a non null int. It doesn't appear to be an auto incrementing field, as if I leave that column out of my insert statement I get an error saying that the <code>serial_no</code> field cannot be null.</p>
  
  <p>So how do I insert the next available number?</p>
  
  <p>I tried this:</p>
  
  <pre><code>INSERT INTO mytable (serial_no) VALUES ( (SELECT MAX(serial_no)+1 FROM mytable))
  </code></pre>
  
  <p>but I get an error saying that subqueries cannot be used in this context.</p>
  
  <p>EDIT: This table is used in an off the shelf product so I cannot change the design and make the serial_no column an auto increment.</p>