Stored Procedure to insert and update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure to insert and update

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-06-15 : 00:13:15

Hi all,

I have 2 tables,A and B with same columns.

Now I want to write a single stored procedure which insert data into table A and in the same time update with the same data into table B .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 03:05:06

Use Composable DML. See http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

N 56°04'39.26"
E 12°55'05.63"

taj
Starting Member

39 Posts

Posted - 2013-06-15 : 07:50:10

quote:
Originally posted by SwePeso

Use Composable DML. See http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

N 56°04'39.26"
E 12°55'05.63"

If you don't mind can u please explain me with an small example.

I request all forum peoples to workout on this and help me out in getting a fine solution for this requirement.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-15 : 09:25:03

quote:
Originally posted by taj

quote:
Originally posted by SwePeso

Use Composable DML. See http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

N 56°04'39.26"
E 12°55'05.63"

If you don't mind can u please explain me with an small example.

I request all forum peoples to workout on this and help me out in getting a fine solution for this requirement.

That article SwePeso pointed to has a simple example; the third piece of code that he is showing there is what you want to use (assuming you are on SQL 2008 or later).

If you need more specific help, post the DDL for your tables, so someone can write a query tailored to that. If you need help in posting the DDL, take a look at this article: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 09:31:46

[code]UPDATE dbo.TableB
SET CustomerID = CustomerID,
Value = 'My New Value',
LastEdited = GETDATE()
OUTPUT inserted.CustomerID
inserted.Value,
inserted.LastEdited
INTO dbo.TableA
(
CustomerID,
Value,
LastEdited
);[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources