Insert column into table and increment row by one

Question

Given the following tables, I am trying to set eventid to the highest value, incremented by one. So max(eventid) + 1. I cannot seem to get the right SQL syntax to accomplish this.

What I have right now that works, but does not give me what I need, is the following. How would I get the eventid to show, in this case, 96740?

INSERT INTO stock_history
            (lastmodby,
             event,
             previous_stock,
             new_stock,
             lastmodified,
             productid)
SELECT '160'                     AS lastmodby,
       'SALE'                    AS event,
       stockstatus               AS previous_stock,
       stockstatus + 1           AS new_stock,
       Getdate()                 AS lastmodified,
       products_joined.productid AS productid
FROM   products_joined
WHERE  productcode = 'abc'

stock_history table

+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+
|   id   | productid | previous_stock | count | new_stock | event | eventid |     lastmodified      | lastmodby |
+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+
| 105619 |      9282 |              9 |     1 |        10 | SALE  |         | 7/24/2015 5:29:00 PM  |       160 |
| 105578 |      9282 |              8 |     1 |         9 | ORDER |   96739 | 7/23/2015 7:30:00 PM  |     37655 |
|  89241 |      9282 |              7 |     1 |         8 | ORDER |   96738 | 6/1/2014 6:06:00 PM   |     30761 |
|  86773 |      9282 |              6 |     1 |         7 | ORDER |   96737 | 4/12/2014 4:36:00 PM  |     29745 |
|  70419 |      9282 |              5 |     1 |         6 | ORDER |   96736 | 5/21/2013 1:17:00 PM  |      1754 |
|  69088 |      9200 |             19 |     1 |        20 | EDIT  |   96735 | 4/28/2013 10:26:00 AM |      1754 |
|  69050 |      9200 |             18 |     1 |        19 | ORDER |   96734 | 4/27/2013 2:17:00 PM  |     23001 |
|  68127 |      9200 |             17 |     1 |        18 | ORDER |   96733 | 4/13/2013 12:34:00 PM |     22674 |
|  67064 |      9200 |             16 |     1 |        17 | ORDER |   96732 | 3/30/2013 9:23:00 AM  |     22327 |
+--------+-----------+----------------+-------+-----------+-------+---------+-----------------------+-----------+

products_joined table

+-------------+-----------+-------------+
| productcode | productid | stockstatus |
+-------------+-----------+-------------+
| abc         |      9282 |           9 |
| xyz         |      9200 |          19 |
+-------------+-----------+-------------+

score 2 · Accepted Answer · edited May 23 '17 at 12:21

You're better off using an IDENTITY column - SQL Server will take care of this much more efficiently than you can, both in terms of performance as in reliability. This question gives you some options on how to do that, e.g.

ALTER TABLE stock_history DROP COLUMN eventid
ALTER TABLE stock_history ADD eventid INT IDENTITY(1,1)

That said, it is technically possible to do this within the query. It might help to know this pattern for situations where an IDENTITY column is not an option.

INSERT INTO stock_history
            (lastmodby,
             event,
             previous_stock,
             new_stock,
             lastmodified,
             productid,
             eventid)
SELECT '160'                     AS lastmodby,
       'SALE'                    AS event,
       stockstatus               AS previous_stock,
       stockstatus + 1           AS new_stock,
       Getdate()                 AS lastmodified,
       products_joined.productid AS productid,
       (SELECT MAX(eventid) + 1 FROM stock_history) AS eventid
FROM   products_joined
WHERE  productcode = 'abc'

Note that this could lead to duplicate eventids if this query is executed multiple times at once.

Your caution is duly noted. The ID column already has identity. — user357034, Jul 25 '15 at 11:45

Insert column into table and increment row by one

1 Answers1