I want to insert rows into a table that has a unique, non auto-incremented primary key.
Is there a native SQL function to evaluate the last key and increment it or do I have to do it in two steps:
key = select max(primary.key) + 1 INSERT INTO dbo.TABLE (primary.key, field1, fiels2) VALUES (KEY, value1, value2) Since it's auto-generated, simply don't provide it:
INSERT INTO bo.TABLE (field1, fiels2) VALUES (value1, value2) Update: that will work if your column is an IDENTITY column.
To provide explicit values to an identity column, you have to do this:
set identity_insert bo.TABLE on INSERT INTO bo.TABLE (primary_key, field1, fiels2) VALUES ((SELECT ISNULL(MAX(id) + 1, 0) FROM bo.Table), value1, value2) set identity_insert bo.TABLE off But there's no compelling reason for doing it this way.
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; 
SERIALIZABLE is equivalent to HOLDLOCK and is a good start but is not enough. Your query can still fail with high concurrency. You must add UPDLOCK, TABLOCKX. The parts of a query are executed over time, and locks are acquired over time--so the INSERT comes after the SELECT. Two clients can SELECT at the same time with fully compatible read locks, then the second one be blocked while the first one converts to an UPDLOCK for its INSERT, then the second one has the old value once the block is over. You need the SELECT to block all readers!SELECT max() + 1 pattern is not best practice and cannot be recommended. Identity columns exist for precisely this reason. Use them.
In my opinion the best answer is to fix your table so that the PK column is an identity column. (Please see my comments on the answer from Sebastian Meine about why your currently selected answer is not best.) The only way to make an existing PK become an identity column is by swapping out the table. Roughly:
BEGIN TRAN; -- Rename all constraints in original table EXEC sp_rename 'dbo.YourOriginalTable.PK_ConstraintName', 'PKConstraint_Backup'; EXEC sp_rename 'dbo.YourOriginalTable.OtherConstraintName', 'OtherConstraintName_Backup'; CREATE TABLE dbo.WorkTable ( YourPKColumn int identity(1, 1) NOT NULL -- your PK converted to identity CONSTRAINT PK_YourOriginalTableConstraintName PRIMARY KEY CLUSTERED, AllOtherColumns -- all your other columns exactly as in the original table ); SET IDENTITY_INSERT dbo.WorkTable ON; INSERT dbo.WorkTable (YourPKColumn, AllOtherColumns) SELECT YourPKColumn, AllOtherColumns FROM dbo.YourOriginalTable WITH (TABLOCKX, HOLDLOCK); SET IDENTITY_INSERT dbo.WorkTable OFF; -- Drop all FK constraints from other tables pointing to your table ALTER TABLE dbo.TableWithFK_1 DROP CONSTRAINT FK_TableWithFK_1_YourOriginalTableSomethingID; -- Swap out the tables EXEC sp_rename 'dbo.YourOriginalTable', 'YourOriginalTableBackup'; EXEC sp_rename 'dbo.WorkTable', 'YourOriginalTable'; -- If you didn't add them in the WorkTable creation, -- add all other removed or needed constraints creation ALTER TABLE dbo.YourOriginalTable ADD CONSTRAINT OriginalConstraint (OriginalConstraintColumns); -- Add back FK constraints from other tables to this one. COMMIT TRAN; You now have a table that has an identity column with a clustered PK on it. You can insert to it no problem. No more concurrency issues and silly SELECT Max() + 1 junk that is so easy to get wrong.
YourOriginalTable after the INSERT but before the renames.Inserting one record:
I only do this to insert a row into a table with a non auto-incremented primary key:
DECLARE @myKey int SET @myKey = (SELECT ISNULL(MAX(primaryKey),0) FROM dbo.myTable) INSERT INTO dbo.myTABLE (primaryKey, field1, fiels2) VALUES (@myKey + 1, value1, value2) Inserting multiple records:
In another situation, I needed to duplicate multiple records in a table. For this, I used an INSERT from the result of a SELECT. My intention was to duplicate some records from myTable, that meet a specific condition. But I needed to figure out how to duplicate records in a table with a unique PRIMARY KEY, without repeating an ID. Here's how I solved it:
DECLARE @myKey int SET @myKey = (SELECT ISNULL(MAX(primaryKey),0) FROM dbo.myTable) INSERT INTO dbo.myTABLE (primaryKey, field1, fiels2) SELECT (primaryKey + @myKey, value1, value2) FROM dbo.myTable WHERE value2 = especific_Condition There were some gaps in the IDs, but I enforce uniqueness PRIMARY KEY.
create table if not exists Emp ( eid int(10) not null primary key auto_increment, name varchar(45) not null, age int(5) default 20, salary int(5) ) insert into emp values(102,'Ranjan',21,450000);
Then try below sql query . It will automaticaly increment the eid to next number .
insert into emp (name,salary) values( 'Lisma',118500);
select * from emp;
CREATE TABLE dbo.Tbl1(id INT PRIMARY KEY, val1 INT, val2 INT) INSERT INTO dbo.Tbl1(id, val1, val2) SELECT (SELECT ISNULL(MAX(id),0) FROM dbo.Tbl1 WITH(SERIALIZABLE, UPDLOCK)+ROW_NUMBER()over(order by (select 0)) , val1 , val2 FROM dbo.Tbl2; 
dbo.Tbl2.
IDENTITYcolumn on the table? If so, what precise problem are you having that is not discussed in the documentation or the many questions on this site about using them? In a comment below you mention an error but you haven't said exactly what it is. Ideally, please show yourCREATE TABLEscript, yourINSERTstatement, and the resulting error message.SET IDENTITY_INSERT sometableWithIdentity ONfrom stackoverflow.com/a/7063527/1548275 is a clear approach to problem as I see it.