• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

how do i duplicate a record in a recordset where the primary key with identity set true

I was trying to create a stored procedure listed below
where the primary key is  RecordNumber  and  identity is true

CREATE PROCEDURE DuplicateRegisterHeader(@RecordNumber varchar(10))
AS
   Begin
     INSERT INTO tRegisterHeader
     SELECT * FROM tRegisterHeader
     WHERE RecordNumber = 278844
   End

this is the returned error msg
Msg 8101, Level 16, State 1, Procedure DuplicateRegisterHeader, Line 4
An explicit value for the identity column in table 'tRegisterHeader' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
rbrown0311
Asked:
rbrown0311
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
You'll need to explicitly list the columns you want to insert, and set IDENTITY_INSERT on, since you're specifying its value:


CREATE PROCEDURE DuplicateRegisterHeader (
    @RecordNumber varchar(10)
)
AS
SET NOCOUNT ON;
SET IDENTITY_INSERT tRegisterHeader ON
INSERT INTO tRegisterHeader ( RecordNumber, ...other_columns... )
SELECT @RecordNumber AS RecordNumber, ...other_columns...
FROM tRegisterHeader
WHERE
    RecordNumber = 278844
GO
0
 
rbrown0311Author Commented:
I've requested that this question be deleted for the following reason:

found solution
0
 
Anthony PerkinsCommented:
I am re-opening this question to allow the author to re-evaluate as a valid solution was offered and they were unable to produce any.  Failing that an EE Moderator will review.
0
 
Anthony PerkinsCommented:
I recommend:
2) http:#a39962609
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now