Creating a duplicate record with a new ID

[Mayoor]

Hi, I need to create a duplicate record of a record in my database and insert it with a new ID.

The ID is an identity field so that will increment once the record is inserted anyway so I guess I just need the SQL that will do this. The only information I have is the ID of the record I want to duplicate.

Any ideas?

 

[SQLDenis]

declare @id int
select @id = 234

insert into tableA (field1,field2)
select field1,field2 from tableA
where ID = @id

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[jbenson001]

Try:

Insert Into <table> (col list excluding identity)
Select (col list excluding identity)
From <table>
Where <table>.id = <some id>

 

[Mayoor]

Nice one guys that did the trick!

 

[jbenson001]

glad to help ..
Jim

 

[Mayoor]

Guys I am now trying to complicate things by using parameters to update the new rows. So what will happen is that a new row is created with the duplicate fields but then there are certain fields I want updated in that new row afterwards with new values, I have tried doing

CREATE PROCEDURE dbo.apCreateDuplicateEvent
@id int,
@varEventPriority int,
@varDelegateID int,  
@varEventStatusID int,
@varDateAssigned datetime,  
@varCompleteID int,  
@varStartDate datetime,  
@varEndDate datetime, 	
@varEventOccStartTime char,   
@varEventOccEndTime char,  
@varDurationID int,  
@varAllDayEvent int

AS

INSERT INTO 	EVENTS ( @varDurationID, @varDateAssigned ,@varCompleteID,@varDelegateID, OutlookString,ReminderID,@varEventPriority,@varStartDate,@varEndDate,OccEndDate, @varEventOccStartTime, @varEventOccEndTime, EventSubject,EventText, @varAllDayEvent, EventOriginatorID,@varEventStatusID,AssScopeId,AssOwnId,AssDefID,DivisionID, NationID, NatRegID,SiteID)
SELECT DurationId,DateAssigned, CompleteID,DelegateID,OutlookString,ReminderID,PriorityID,StartDate,EndDate,OccEndDate, StartTime, EndTime, EventSubject,EventText, AllDayEvent, EventOriginatorID,EventStatusID,AssScopeId,AssOwnId,AssDefID,DivisionID, NationID, NatRegID,SiteID from EVENTS
WHERE 	EventID = @id
GO

but get this error

Incorrect syntax Near 'outlookstring'

any ideas?

 

[r937]

you put the @'s in the wrong place

the INSERT INTO should list only actual column names

the SELECT can have a mix of column names and parameter values


r937.com | rudy.ca