Solved

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

Posted on 2014-03-28
6
211 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
  • 2
6 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
I've requested that this question be deleted for the following reason:

found solution
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
I recommend:
2) http:#a39962609
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now