How to increment a primary key in an insert statement in SQL Server 2005

I need to write an insert statement into a table the columns looks like this

• demandtypeid (PK, FK, int, not null)
• characvalueid (PK, FK, int, not null)
• percentage (int null)
• lastuser (varchar(100), null)
• lastedited (datetime, null)

Here is the INSERT statement. Notice the there is not values at the

value( ,  , 'Bob')

as I think that’s where the auto-increment command should go

insert into tr_demandtypecharac(demandtypeID, characvalueid, lastuser) 
values(  , , 'Bob')

Please help with a simple little statement

I just want to know how to manually insert into this table

Here’s my table structure:

CREATE TABLE [dbo].[tr_demandtypecharac](
[demandtypeid] [int] NOT NULL,
[characvalueid] [int] NOT NULL,
[percentage] [int] NULL,
[lastuser] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastedited] [datetime] NULL,
 CONSTRAINT [PK_tr_dtc_pkey] PRIMARY KEY CLUSTERED 
(
[demandtypeid] ASC,
[characvalueid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 ) ON [PRIMARY]

ALTER TABLE [dbo].[tr_demandtypecharac]  WITH CHECK 
ADD CONSTRAINT [FK_tr_dtc_cvid] 
FOREIGN KEY([characvalueid]) REFERENCES [dbo].[tr_characvalue] ([characvalueid])

ALTER TABLE [dbo].[tr_demandtypecharac]  WITH CHECK 
ADD CONSTRAINT [FK_tr_dtc_dtid] 
FOREIGN KEY([demandtypeid]) REFERENCES [dbo].[tr_demandtype] ([demandtypeid])

Answer

Given the CREATE TABLE statement you posted, without auto-increment (aka identity) columns, you would insert providing all columns and values, like this:

insert into tr_demandtypecharac(
       demandtypeid, characvalueid, 
       percentage, lastuser, lastedited) 
values(2, 3, 80, 'Bob', '01/01/2012')

If, however, you do make them auto-increment by changing the CREATE TABLE to:

CREATE TABLE [dbo].[tr_demandtypecharac](
[demandtypeid] [int] NOT NULL IDENTITY(1,1),
[characvalueid] [int] NOT NULL IDENTITY(1,1),
[percentage] [int] NULL,
[lastuser] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastedited] [datetime] NULL,
 CONSTRAINT [PK_tr_dtc_pkey] PRIMARY KEY CLUSTERED 
(
[demandtypeid] ASC,
[characvalueid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 )

Then you would insert providing all non-identity (non-autoincrement) columns like this:

insert into tr_demandtypecharac(
      percentage, lastuser, 
      lastedited) 
values(80, 'Bob', '01/01/2012')

However, it is not common to have more than one column as an identity (autoincrement) column, and generally, this column is the only PRIMARY KEY column.