Sql-How to Insert Row Without Auto Incrementing a Id 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

Insert rows into a table with a increment column without auto increment

Get the maximum value and add it to a ROW_NUMBER() column during the select.

 (
   ID      int primary key
 , name    nvarchar(100)
 , Address nvarchar(1000)
 );

INSERT INTO Person
VALUES
      (122, 'John Doe', 'Some Address 123')
    , (123, 'Homer Simpson', 'Some Address 456')
    , (124, 'Jane Doe', 'Some Address 789')
    , (125, 'Bo Katan', 'Some Address 101112');
    
DECLARE @Persons TABLE
 (
   ID      int primary key
 , name    nvarchar(100)
 , Address nvarchar(1000)
 );
 
INSERT INTO @Persons
VALUES
      (2, 'Quinn Amaro','New Address a')
    , (3, 'Elenor Barreras','New Address B')
    , (4, 'Mckinley Dart','New Address c')
    , (5, 'Ronnie Tank','New Address D')
    , (6, 'Woodrow Creek','New Address e')
    , (7, 'Brittany Patlan','New Address F')
    , (8, 'Len Venzon','New Address g')
    , (9, 'Ila Goodlow','New Address H')
    , (10, 'Velma Tallarico','New Address i')
    , (11, 'Blossom Hanney','New Address J');

INSERT INTO Person (ID, name, Address)    
SELECT
      ID = ROW_NUMBER() OVER(ORDER BY ID) + (SELECT MAX(ID) FROM Person)
    , name
    , Address
FROM @Persons;

SELECT *
FROM Person;

Example db fiddle

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')

Insert rows in table without affecting AUTO_INCREMENT in MariaDB

MySQL and MariaDB actually enforce the restriction AUTO_INCREMENT > MAX(id)

See ALTER TABLE Syntax

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

You can use ALTER TABLE to set the AUTO_INCREMENT to any value higher than MAX(id) if you would like to store higher values, however you cannot set it to a lower value than one of the rows currently in the table.

If you need to create rows in a "gap", with lower IDs than the AUTO_INCREMENT value, you would need to explicitly specify the id value in your INSERT. But if a process beyond your control is inserting rows and not specifying the IDs then they are always going to obtain IDs higher than everything else currently in the table.

The only thing I can suggest, if you are able to adjust what IDs are used for what, is that you reserve low IDs for your purposes (so use, say, 1 to 10,000 instead of 50,000,000 to 50,009,999), set the AUTO_INCREMENT to 10,001 and then let the outside process use the higher IDs - this would work just fine provided you don't run out of space.

For a longer term solution, consider switching to UUIDs - though you would need to modify the process that is outside your control for this.

INSERT INTO statement that copies rows and auto-increments non-identity key ID column

Instead of + 1, add the row number. I also fixed the error in your WHERE clause (should be GroupID =, not ID =):

INSERT INTO MyTable
(       [ID]
       ,[GroupID]
       ,[SomeValue]
)
(
    SELECT
       (SELECT MAX(ID) FROM MyTable) + ROW_NUMBER() OVER (ORDER BY GroupId),
       @NewGroupID,
       [SomeValue]
    FROM MyTable
    WHERE GroupID = @OriginalGroupID
)

How to Insert auto increment ID where column not auto_increment

I couldnt post this as a comment as I dont have enough reputation to perform that action.

Try looking at this link : SQL Server, How to set auto increment after creating a table without data loss?

Might be helpful.

How can I insert data without specifying a value for a non auto-increment primary key?

I have seen this implemented as

• DEFAULT value on the PK column (say -9999)
• A table that holds the Current_PK value
• An INSERT trigger that changes that PK from -9999 to Current_PK and increments Current_PK

Another way that I have seen is to get the MAX from the table and increment it and put it into the new row.

I must say, that both these methods caused bad data, caused locking and blocking and degraded performance.

In your case, I do not see a way other than actually specifying a value for PK.

---

Related Topics