?
Solved

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

Posted on 2014-03-28
6
Medium Priority
?
219 Views
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))
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
Comment
Question by:rbrown0311
[X]
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
6 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 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)
)
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
 

Author Comment

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

found solution
0
 
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.
0
 
LVL 75

Expert Comment

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 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