update using stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 11-Apr-11 0:00am

pallaviAshwath

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2011-04-11T06:16:00

Solution 6

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.

Posted 11-Apr-11 6:16am

Wendelius

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.

Pratik Bhesaniya · Answer 2 · 2011-04-11T00:07:00

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

Mahendra.p25 · Answer 3 · 2011-04-11T00:07:00

Solution 2

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

Posted 11-Apr-11 0:07am

Mahendra.p25

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 .......?"

Santoshshrest · Answer 4 · 2011-04-11T00:10:00

Solution 3

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

Posted 11-Apr-11 0:10am

Santoshshrest

Comments

Henry Minute 11-Apr-11 14:17pm

How does this answer the question asked?

Please see the comment to mahen25s answer.

Santoshshrest · Answer 5 · 2011-04-11T00:19:00

Solution 5

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

Posted 11-Apr-11 0:19am

Santoshshrest

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.

update using stored procedure

5 solutions

Solution 6

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us