Solved

Stored procedure to duplicate a record.

Posted on 2002-06-08
4
178 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
  • 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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