Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hello All,
 
I want create procedure in sql server 2005..for insert and update record.
i need to take the existing record from first table (say employee table)
to second table (say Backup table) and after
that i have to store the new updated record in the first table (i.e employee table)
like that so how can we achieve that task using stored procedure..
If any know then pls help me out..
 
Thanks,
Posted 27-Jul-12 5:07am
Comments
Sandeep Mewara at 27-Jul-12 15:34pm
   
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.

Try them and tell if you face issues.
Members will be more than happy to help like this.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try the below approach.
Here first we retrieve the employee record that will be updated in Employee table and insert it into the backup table. Then the employee record in Employee table is updated. Here the stored procedure is only updating the employee name and email address. You can update any other Employee fields you want in your stored procedure.
 
CREATE PROCEDURE usp_UpdateEmployee
@EmployeeID INT,
@EmployeeName VARCHAR(50),
@Email VARCHAR(50)
 

AS
 
BEGIN
BEGIN TRY        
  BEGIN TRANSACTION   
 

	--Retreive the employee that will be updated in employee table and insert into backup table
	INSERT INTO [BackUp]
	(
		EmployeeID,
		EmployeeName,
		Email
	)
	SELECT EmployeeID,EmployeeName, Email FROM Employee WHERE EmployeeID =  @EmployeeID
 
	--Update the data in employee table for the given EmployeeID
	UPDATE Employee
	SET EmployeeName = @EmployeeName
		Email = @Email
	WHERE EmployeeID =  @EmployeeID
	
 
                    
  COMMIT TRANSACTION        
 END TRY        
 BEGIN CATCH        
  ROLLBACK TRANSACTION        
  RAISERROR(ERROR_MESSAGE(),
	ERROR_SEVERITY(),
	1,
	ERROR_NUMBER(), 
	ERROR_SEVERITY(),  
	ERROR_STATE(),
	ERROR_PROCEDURE(),
	ERROR_LINE()
	)      
 END CATCH        
END        
       
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insstud]
@name varchar(500),
@class varchar(500),
@add varchar(500),
@StatementType nvarchar(20)
AS
BEGIN
-- INSERT INTO stud(name1, class, add1) VALUES(@name, @class, @add)
 
IF @StatementType = 'Insert'
BEGIN
INSERT INTO stud(name1, class, add1) VALUES(@name, @class, @add)
--insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name, @last_name, @salary, @city)
END

IF @StatementType = 'Select'
BEGIN
select * from stud
END

IF @StatementType = 'Update'
BEGIN
UPDATE stud SET
name1 = @name, class = @class, add1 = @add
WHERE name1 = @name
END

else IF @StatementType = 'Delete'
BEGIN
DELETE FROM stud WHERE name1 = @name
END
END
  Permalink  
Comments
CHill60 at 2-Aug-13 12:56pm
   
Where's the backup that the OP required (a year ago)?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 434
1 Nirav Prabtani 338
2 Abhinav S 170
3 PIEBALDconsult 160
4 Dave Kreskowiak 155
0 OriginalGriff 7,640
1 Sergey Alexandrovich Kryukov 6,836
2 Maciej Los 3,919
3 Peter Leow 3,698
4 CHill60 2,742


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 2 Aug 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100