Solved

Stored procedure to duplicate a record.

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 45
Help in setting the first day of the week to Monday 6 23
c# code 19 59
Calculating Business Hours 19 63
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now