CODEWITHSUNDEEP
sqlsql-serverinsert
Muidul Alam 2hin
Muidul Alam 2hin

Reputation: 189

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

I have a table Person with this structure:

[ID] int PRIMARY KEY
[Name] nvarchar(100)
[Address] nvarchar(1000)

I can't use auto-increment on the ID column.

I have to insert 10 rows (which exist in @Persons table variable) into the Person table and I know current the max ID is 125.

How can I insert those 10 rows into the Person table without using a loop or cursor?

Upvotes: 0

Views: 1681

Answers (1)

George Frazee
George Frazee

Reputation: 66

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

Upvotes: 4

Related Questions