Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored procedure to duplicate a record.

Posted on 2002-06-08
4
Medium Priority
?
191 Views
Last Modified: 2012-08-13
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
Comment
Question by:Wimmeke
[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
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7065328
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
 
LVL 5

Accepted Solution

by:
spcmnspff earned 800 total points
ID: 7066938
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
 
LVL 1

Author Comment

by:Wimmeke
ID: 7071271
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
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7071287
Thanks, I just wanted to make sure the thing behaved exactly as you asked . . . =)
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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 ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

705 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