Click here to Skip to main content
15,947,411 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi i want create stored procedure in sql2008 to update data from one table to another table help for this


thanks in advance
pallavi
Posted

This can be done in several ways. One way is to use join as described in earlier post, another could be to use correlated (or non-correlated) sub-queries. One simple example:
UPDATE MyTable
SET Field1 = (SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField)
WHERE ...


If you could write the requirements and perhaps an example, then the solutions could be targeted to your psecific problem.
 
Share this answer
 
Comments
mhwasim 18-May-11 9:44am    
The above solution has 2 issues
1. The query SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField should not return more than 1 record
2. if No data found then Field1 will be poopulated with Null which I dont think so is rite solution.
Wendelius 21-May-11 14:20pm    
Bullet 1 is correct but since there was no additional information fro the OP this is just an example. For the bullet 2: depending on the situation, null may/may not be the correct value for the update. Again, more information would be needed about the situtation.
Hi try this,

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO


Results after the update:

a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z
 
Share this answer
 
Try this
Its An example

create procedure sp_Updatedata  
(  
@OrderId int,  
@ShipName varchar(50),  
@ShipCountry varchar(50),  
@ShipMethodId int  
)  
  
as  
  
update Orders set ShipName=@ShipName,ShipCountry=@ShipCountry,ShipMethodId=@ShipMethodId where OrderId=@OrderId
 
Share this answer
 
v2
Comments
Henry Minute 11-Apr-11 14:14pm    
How does this help the OP update from one table to another?
How is this a stored procedure?

It is excellent that you want to help others but you should try to actually answer their question, or explain why you aren't. For example "I do not think a stored procedure is the best way to do what you want. Have you tried .......?"
Is this the code you are looking for

-----------------------------------------

CREATE PROCEDURE [dbo].[SP_MARINE_SYSTEM_USER_REGISTER_LOGIN_INFORMATION]
	(
		@SystemUserID NVARCHAR(50)

	)
AS
	UPDATE TBL_DETAILS SET SYSTEMUSERID=@SystemUserID
	RETURN
 
Share this answer
 
Comments
Henry Minute 11-Apr-11 14:17pm    
How does this answer the question asked?

Please see the comment to mahen25s answer.
I think this might help you...

--------------------------------
UPDATE TABLEA
SET     b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO
 
Share this answer
 
Comments
Henry Minute 11-Apr-11 14:17pm    
This would be quite slow and inefficient but is far better than your other answer as it actually answers the question.

The only problem is that it is an almost exact copy of an answer given an hour before yours.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900