-1

I am trying to add a large number of rows into a SQL Server table, but I need to sequence the numbers by adding 1 to two columns in the Insert INTO command. How can I tell the next line that is added to add one to the previous number for a specific column?

I am trying to add to the PartID and Sequence columns when the column does not allow duplicate numbers?

The is the query I am using

INSERT INTO JobParts(JPListID, PartID, Quantity, ReplacementPercent, Sequence, FlatRateExceptCode, Fluid, Replaced)
VALUES ('1', '670', '1', '100.00', '25', '0', 'False', 'False');
go 50
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3233445
  • 1
  • Which version of SQL Server? – Hamlet Hakobyan Jan 24 '14 at 20:14
  • Hmmm, you are passing in string values for both `PartId` and `Sequence`. Your request doesn't make sense. – Gordon Linoff Jan 24 '14 at 20:19
  • Ya, those were a starting point. I need to tell PartId and Sequence to add one to the existing number in each of those columns. I need to start PartId with 670 then increment by 1 each time, and Sequence needs to start with 1 and then increment by 1 each time. – user3233445 Jan 24 '14 at 20:22
  • I am using MSSQL 2008 – user3233445 Jan 24 '14 at 20:22
  • I have about 20k lines to add. All the other fields stay the same numbers. PartId and Sequence are the only numbers that need to be sequenced – user3233445 Jan 24 '14 at 20:25
  • This is the error I get – user3233445 Jan 24 '14 at 20:27
  • Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.JobParts' with unique index 'IDX_Sequence'. The statement has been terminated. ** An error was encountered during execution of batch. Continuing. – user3233445 Jan 24 '14 at 20:27
  • `IDENTITY` would be what you are looking for. Can you alter the `JobParts` table? If not you can first insert into a temp table and from there to `JobParts`. – TomT Jan 24 '14 at 23:38
  • Please don't use string literals for numbers. `'1'` is a string/character value, `1` is a number. –  Jan 30 '14 at 19:59

1 Answers1

0

You can use IDENTITY property on the column to make SQL Server automatically increment your desired column. Note that you can not add IDENTITY to the column after the fact. This has to be done during creation of the table or it can be done by deleting old column and adding new column with IDENTITY property.

If you already have date and don't want to lose anything take a look at this questions Adding an identity to an existing column

Full documentation on `IDENTITY can be found here

Community
  • 1
  • 1