CODEWITHSUNDEEP
sqlsql-server-2008t-sqlauto-increment
Jan Van Looveren
Jan Van Looveren

Reputation: 918

Insert rows autoincrement on multiple columns

I have following table

tbl_Users 

Id , UserFile, Name  
1,1200,Jan  
2,1201,Piet  
3,1202,Joris

Column one is already an identity column. Now I want to insert a batch from contact persons to new users. 

INSERT INTO tbl_Users (UserFile,Name)  
    SELECT [AutoIncrement], Name 
    FROM tbl_ContactPerson 
    WHERE (ContactType = 'U')

My problem is at [AutoIncrement], how can I add +1 to the UserFile column so I have an autoincrement on that column as well?

I am using SQL Server 2008R2

Kind regards

Upvotes: 2

Views: 9631

Answers (3)

Neil Moss
Neil Moss

Reputation: 6838

Try using the ROW_NUMBER() function in your "batch" select:

declare @StartValue int
SELECT @StartValue = MAX(UserFile) FROM tbl_Users 

INSERT INTO tbl_Users (UserFile,Name)   
    SELECT 
        (@StartValue + ROW_NUMBER() over (order by (select 1))) as IncrementNumber, 
        Name  
    FROM 
        tbl_ContactPerson  
    WHERE 
        (ContactType = 'U')

The (order by (select 1)) is a small hack to number the rows in the order they were returned by the select.

You'll need to seed the @StartValue with an appropriate base number. I've used the previous max of the UserFile value.

EDIT Note Andriy M's comment below about atomicity. In the example above, you'd need at least a repeatable-read transaction to ensure consistency.

Upvotes: 8

user653649
user653649

Reputation: 115

DECLARE @seed int
SELECT @seed = MAX(UserFile) FROM tbl_Users

CREATE TABLE #temp_users
(
  UserFile identity(@seed + 1, 1)
, Name varchar(max)
)

INSERT INTO #temp_users
  SELECT 
    Name 
  FROM 
    tbl_ContactPerson with (nolock)
  WHERE 
    (ContactType = 'U')

INSERT INTO tbl_Users
(
  UserFile
, Name
)
SELECT
  UserFile
, Name
FROM
  #temp_users

DROP TABLE #temp_users

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Set the UserFile column to auto_increment too and leave it empty in your select query

INSERT INTO tbl_Users (UserFile,Name)  
    SELECT Name FROM tbl_ContactPerson WHERE (ContactType = 'U')

or do this

INSERT INTO tbl_Users (UserFile,Name)  
    SELECT Max(UserFile) + 1, Name FROM tbl_ContactPerson WHERE (ContactType = 'U')

Upvotes: 0

Related Questions