Insert Manually into a Table by SQL Statement, But Key Is Autoincremented

Insert manually into a table by SQL statement, but key is autoincremented

So just don't use it...do it like this..

And be sure you use single quotes for inserting strings

INSERT INTO table_name (username, password)
VALUES ('Mike', 'Mike');

As you said your field is auto incremented, SQL will automatically increment the value of the id field by 1

How to insert an auto_increment key into SQL Server table

Judging by you comments throughout, you have a primary key on the table that is not an identity column.

If your version of SQL Server is SQL 2012 you should look into sequences: http://msdn.microsoft.com/en-us/library/ff878091.aspx

In other versions you either need to recreate the table using the IDENTITY property (http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx) for the primary key column or use a two step approach.

If you go with the two step approach you need to make sure that concurrently running inserts won't end up using the same new value. The easiest way to do that is this is by combining the select and the insert into one value and using the serializable table hint:

CREATE TABLE dbo.Tbl1(id INT PRIMARY KEY, val1 INT, val2 INT)

INSERT INTO dbo.Tbl1(id, val1, val2)
VALUES((SELECT ISNULL(MAX(id)+1,0) FROM dbo.Tbl1 WITH(SERIALIZABLE, UPDLOCK)), 42, 47);

SELECT * FROM dbo.Tbl1;

How to insert new row to database with AUTO_INCREMENT column without specifying column names?

For some databases, you can just explicitly insert a NULL into the auto_increment column:

INSERT INTO table_name VALUES (NULL, 'my name', 'my group')

How to insert data to MySQL with auto-incremented column(field)?

In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.

INSERT INTO test.authors (
   instance_id,host_object_id,check_type,is_raw_check,
   current_check_attempt,max_check_attempts,state,state_type,
   start_time,start_time_usec,end_time,end_time_usec,command_object_id,
   command_args,command_line,timeout,early_timeout,execution_time,
   latency,return_code,output,long_output,perfdata
) VALUES (
   '1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
   '2012-01-03 12:50:59','198963','21','',
   '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
   '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
   '','rta=2.860000m=0%;80;100;0'
);

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

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.

SQL-How to Insert Row Without Auto incrementing a ID Column?

If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off

---

Related Topics