SQL for duplicating records

Question

SQL for duplicating records

kevin-975129

Newbie

Points: 2
More actions
December 25, 2008 at 9:13 pm

#197785

Hi
Here is a simplified example of what I am working on:
CREATE TABLE [dbo].[Person](
[PersonID] [uniqueidentifier] NOT NULL,
[Surname] [nvarchar](40) NULL)
CREATE TABLE [dbo].[Address](
[AddressID] [uniqueidentifier] NOT NULL,
[AddressLine] [nvarchar](40) NULL)
CREATE TABLE [dbo].[PersonAddress](
[PersonID] [uniqueidentifier] NOT NULL,
[AddressID] [uniqueidentifier] NOT NULL,
[AddressType] tinyint NOT NULL)
The uniqueidentifiers in the first two tables are the primary key, and in the second table the two uids make up the primary key. (Obviously there are far more columns in the real database)
So we have a person, who can have any number of addresses via the linking table (PersonAddress).
What I would like to do is duplicate this Person and all the Addresses (this gives a starting point for the user to create a new person).
So I have the following (not tested and probably contains a mistake or two, but hopefully you get the idea):
CREATE PROCEDURE Duplicate_Person
@personId UNIQUEIDENTIFIER
--first create the new person
DECLARE newPersonId uniqueidentifier
SET @newPersonId = NEWID()
INSERT INTO Person (PersonId, Surname)
SELECT @newPersonId, Surname
FROM Person
WHERE PersonId = @personId
At which point I got stuck. Lets say the person has 5 Addresses, I need to duplicate those 5 addresses but I need to keep track of the new AddressId's so that I can populate the linking table with them.
I did think about doing this in a cursor, but I have about 40 columns, so it seemed like I would have to create a variable for each column, open each of the records in my cursor into all those variables and then insert them sequentially into the Address table and then the PersonAddress table. This seems like a long-winded and slow solution.
Is there any way I can do it in a single statement? I feel like I am missing something very obvious here.
Thanks in advance
Kevin

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jon-474332 SSC Eights! Points: 839 More actions · Answer 1

if you got a single line solution feel free to post it, i'll like to take a look at it. Here's my suggestion (not tested) using a table var as the cost of IO is much less than that of a cursor:

CREATE PROCEDURE Duplicate_Person

@personId UNIQUEIDENTIFIER

--first create the new person

DECLARE newPersonId uniqueidentifier

SET @newPersonId = NEWID()

INSERT INTO Person (PersonId, Surname)

SELECT @newPersonId, Surname

FROM Person

WHERE PersonId = @personId

DECLARE @PA TABLE

(

AddressID UNIQUEIDENTIFIER,

AddressLine NVARCHAR(40),

AddressType TINYINT

)

INSERT INTO @PA

SELECT NEWID() AS AddressID, a.AddressLine, b.AddressType

FROM Address AS a INNER JOIN PersonAddress AS b ON a.AddressID = b.AddressID

WHERE (b.PersonId = @personId)

INSERT INTO Address

SELECT AddressID, AddressLine FROM @PA

INSERT INTO PersonAddress

SELECT @personId AS PersonID, AddressID, AddressType FROM @PA

cheers,

jon 😉

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

Hi Kevin,

Look at the OUTPUT clause in books on line.

Regards

Piotr

...and your only reply is slàinte mhath

jon-474332 SSC Eights! Points: 839 More actions · Answer 3

nice point.. but i'll use output clause only when i know how many values to return. If the number of address is "n" number of records then you'll have to use xml datatypes.

Personally if "n" is unknow i'll simply do a select and return all those keys, normally i'll use output clause only to return not more than 2 values from a sp.

cheers,

jon 😉

kevin-975129 Newbie Points: 2 More actions · Answer 4

Thanks Jon that's great! This works a treat.

Kevin