Cursor to insert a duplicate row battle with Identity column

Question

Post reply

Cursor to insert a duplicate row battle with Identity column

Bharti Moorjani

SSC-Addicted

Points: 480
More actions
October 18, 2005 at 12:08 pm

#169368

Please help.
I am trying to add a duplicate row to the employee talble with one variation
this is a test case for mass duplicate insertions..
Declare @EmployeeID int
Declare @LastName nvarchar(20)
Declare @FirstName nvarchar(10)
Declare @Title nvarchar(30)
Declare @TitleOfCourtesy nvarchar(25)
Declare @BirthDate    datetime
Declare @HireDate datetime
Declare @Address nvarchar(60)
Declare @City nvarchar(15)
Declare @Region nvarchar(15)
Declare @PostalCode nvarchar(10)
Declare @Country nvarchar(15)
Declare @HomePhone nvarchar(24)
Declare @Extension nvarchar(4)
--Declare @Photo image
--Declare @Notes ntext
Declare @ReportsTo int
Declare @PhotoPath nvarchar(255)

declare mycursor cursor for (Select top 1 EmployeeID ,LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address , City, Region, PostalCode, Country,   HomePhone, Extension,   ReportsTo, PhotoPath
      From Employees
     Where Lastname = 'Davolio' AND
               Birthdate ='12/8/1948'

  &nbsp
open mycursor fetch next from mycursor into @EmployeeID , @LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address , @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @ReportsTo, @PhotoPath

while @@fetch_status = 0
Begin

SET IDENTITY_INSERT Employees ON
set @Birthdate =18/8/1988

  set @EmployeeID =Ident_current ('employees') + 1
  insert into #tememp

  Select
  EmployeeID = @EmployeeID,
  LastName= @LastName,
  FirstName= @FirstName,
  Title=@Title,
  TitleOfCourtesy=@TitleOfCourtesy,
  BirthDate=@BirthDate,
  HireDate=@HireDate,
  Address=@Address ,
  City=@City,
  Region=@Region,
  ostalCode=@PostalCode">PostalCode=@PostalCode,
  Country=@Country,
  HomePhone=@HomePhone,
  Extension=@Extension,
  Photo= null,
  Notes = null ,
  ReportsTo=@ReportsTo,
  hotoPath=@PhotoPath">PhotoPath=@PhotoPath
   From Employees
     Where Lastname = 'Davolio' AND
               Birthdate ='12/8/1948'

  set nocount on
fetch next from mycursor into @EmployeeID , @LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address , @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @ReportsTo, @PhotoPath
end
Close mycursor
Deallocate mycursor

results in the following
An explicit value for the identity column in table 'Employees' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

Have you tried reading the help about identity insert?

There's a clue in the message even: column list is required.

It is the nature of identity columns to increment themselves, why do you feel compelled to use a cursor anyway? Why have an identity column if you want to control the value?