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

Posted on 2014-03-28
Last Modified: 2014-04-11
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))
     INSERT INTO tRegisterHeader
     SELECT * FROM tRegisterHeader
     WHERE RecordNumber = 278844

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.
Question by:rbrown0311
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 69

Accepted Solution

Scott Pletcher earned 500 total points
ID: 39962609
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)
INSERT INTO tRegisterHeader ( RecordNumber, ...other_columns... )
SELECT @RecordNumber AS RecordNumber, ...other_columns...
FROM tRegisterHeader
    RecordNumber = 278844

Author Comment

ID: 39970864
I've requested that this question be deleted for the following reason:

found solution
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39970866
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39980710
I recommend:
2) http:#a39962609

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with a query 14 37
Related to SQL Query 5 21
Need quicker response from an Execption table 11 21
Need multiple Group By's 8 25
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question