Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Stored procedure to duplicate a record.

Probably a basic question for most of you ...
So here we go:

I have two tables: (Example)

table1
   id
   firstname
table2
   id

I want a stored procedure that will take three parameters
existing id, new id and new firstname.

The stored procedur should look up the 'existing id' in table1 and copy that row (record) to a new row with the parameter 'new id' as the data for the id field in table1.

At the same time a new record should be created in table2, also with the parameter 'new id' as the id.

There should be a check for duplicates and an error message needs to be generated if you would ask it to create a new id which already exists.

Hope you guys get this poor explanation in bad english :)

Many thanks in advance,

Wim
0
Wimmeke
Asked:
Wimmeke
  • 2
1 Solution
 
Anthony PerkinsCommented:
It would seem to me from your explanation, that the third parameter new firstname is not needed.  Therefore

Create Procedure @ID int , @NewID int

As

Insert Table1
Select @NewID As ID,
       FirstName
From   Table1
Where  ID = @ID
       And NewID Not In (Select ID From Table1)

If @@ROWCOUNT
   Insert Table2
   Select @NewID As ID
   From   Table2
   Where  @NewID Not In (Select ID From Table2)

Anthony
0
 
spcmnspffCommented:
Create Proc spNewId
     @OldId Int,
     @NewId Int,
     @NewName VarChar(20)
AS


If Exists(Select ID from Table1 where ID = @NewID)
BEGIN
    Raiserror('Pre-Existing ID value requested for NewId Parameter.  Insert Fails',16,1)
END
ELSE
BEGIN

    Begin Tran
     Insert Into Table1
     Select @NewId, @NewName, Col1, Col2, . . ., ColN
     FROM Table1
     Where ID = @OldID

     Insert Table2 (ID)
     Select @NewID
    Commit Tran
END
0
 
WimmekeAuthor Commented:
Great,

I repeated the raiserror part to check for duplicates in the second table as well. Thanks for your help.

I chose your comment as answer since it had the raiserror part in it, but for the rest Acperkins comment would have done the thing as well.

Thanks,

Wim Toremans
0
 
spcmnspffCommented:
Thanks, I just wanted to make sure the thing behaved exactly as you asked . . . =)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now