Auto increment index column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Auto increment index column

Author

Topic

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-20 : 11:26:56

Being new on creating my own SQL table, I am bumping into this issue:

I would like to auto increment the first column (index) of my table, as soon as I add a new record.
This is the code I have:


CREATE TABLE [dbo].[_FO_QP_QuoteList]
(
	 [QuoteNumber]	[int]			NOT NULL
	,[Field2]	[int]			NOT NULL
	,[Field3]	[nvarchar](30)		NULL
	,[Field4]	[nvarchar](30)		NULL

 CONSTRAINT [PK__FO_QP_QuoteList] PRIMARY KEY CLUSTERED 
(
	[QuoteNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

What should I add to auto increment the QuoteNumber?

Thanks for helping me out.
Arnold

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-20 : 11:30:09

You need to add IDENTITY:

CREATE TABLE [dbo].[_FO_QP_QuoteList]
(
	 [QuoteNumber]	[int] IDENTITY(1, 1) NOT NULL
	,[Field2]	[int]			NOT NULL
	,[Field3]	[nvarchar](30)		NULL
	,[Field4]	[nvarchar](30)		NULL

 CONSTRAINT [PK__FO_QP_QuoteList] PRIMARY KEY CLUSTERED 
(
	[QuoteNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-20 : 11:46:50

Thanks for your answer Lamprey,

I have tried it, but when inserting a row (via Excel) I get this error:


Error -2147217900: [Microsoft][ODBC SQL Server Driver][SQL Server]An explicit value for the identity column in table '_FO_QP_QuoteList' can only be specified when a column list is used and IDENTITY_INSERT is ON

Do you have an idea what to add to solve this error?

Arnold

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-20 : 11:53:35

Oh.. I assume I have to fix that in the Insert query and NOT the creation of the table.
I will try to fix that first.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-20 : 13:05:48

Not sure why you want an IDENTITY column when you are specifying the number. But, you need to put the SET IDENTITY INSERT statements inside the stored procedure, so that you can override the IDENTITY when you do you insert.

ArnoldG
Starting Member

36 Posts

Posted - 2014-06-20 : 15:00:25

Thanks,
That works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources