-1
INSERT INTO [db].[schema].[table1] 
      ([id], [status], [date], [username], 
       [date2], [username2], [description])
VALUES (1, 'A', '2021-01-01T05:30:44', 'abc', 
        '2021-04-01T05:30:44', 'xyz', 'DESC')
GO 10

I want to auto increment the primary key so I can insert 10 records with different primary keys of 1,2,3,4...etc.

Is there a way to do this?

The table is already created, I can't change it. Would it be better to create another table and insert the data from there into my table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
carrotcake
  • 27
  • 4
  • If by chance the `id` column is already an `identity` column, you can simply remove `id` from the insert statement `INSERT INTO [db].[schema].[table1] ([status], ...) values ('A', ...)` and the id will be inceremented automatically. – Olivier Jacot-Descombes Oct 27 '22 at 16:53

1 Answers1

3

The normal way to do this is define the id column as an identity value, and then omit the column from the insert statement. Then SQL Server will generate the value for the id column automatically.

But I see this:

The table is already created, I can't change it.

Unfortunately, existing columns cannot become identity columns. You would have to add a new identity column with the old values, and then remove the original column.

But there is still hope. Another option is creating a Sequence, and then using the NEXT VALUE FOR syntax in your INSERT statement. Once you do this, you want to be sure to always use the sequence, and never insert new values without it. Otherwise you could end up trying to create duplicate keys, which will fail.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 1
    It could well be worth also using a `DEFAULT` `CONSTRAINT` on the column so that it can be omitted in the `INSERT` and the `SEQUENCE` used by default. – Thom A Oct 27 '22 at 17:08
  • Ok, I added CREATE SEQUENCE auto_increment START WITH 1 INCREMENT BY 1 But when I try to use it in my INSERT statement: VALUES(auto_increment.nextval, 1, 'A', ....etc I get an error: The multi-part identifier "auto_increment.nextval" could not be bound. – carrotcake Oct 27 '22 at 17:16
  • 1
    In SQL Server the syntax is `(next value for SequenceName)` not `SequenceName.nextval`. – David Browne - Microsoft Oct 27 '22 at 17:23
  • Thank you! That worked. Is there a way to auto-increment letters using sequence? – carrotcake Oct 27 '22 at 17:47
  • If that worked, please click the check mark next to the answer to mark it as accepted and to award reputation points. You can also upvote good answers (and even comments). This is how we say "thank you" on stackoverflow. – Olivier Jacot-Descombes Oct 28 '22 at 14:25
  • There's nothing built in for letters. You can use a computed column to append the value to an existing text prefix or add the increment to an ascii 'A', but there's nothing to make it wrap correctly; you have to write the code for that math yourself. – Joel Coehoorn Oct 28 '22 at 14:28