Solved

Stored procedure to duplicate a record.

Posted on 2002-06-08
4
188 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 200 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 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