locked
Updating the Table using stored procedure RRS feed

  • Question

  • Hi,

    by using stored procedure ..i am updating my table.

    but my requirement is...before updating the table....that data should insert into other table.

    any suggestions?

    Tuesday, March 19, 2013 11:39 AM

Answers

All replies

  • Well. What is the problem? To write the insert statement? Or what?

    Also, please post the script to create both tables. We cannot write a stored procedure that do what you want if you don't provide the tables.

    • Proposed as answer by Naomi N Wednesday, March 20, 2013 2:03 AM
    Tuesday, March 19, 2013 11:44 AM
  • create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Sarat (SS) Tuesday, March 19, 2013 12:14 PM
    • Marked as answer by Allen Li - MSFT Tuesday, March 26, 2013 2:07 AM
    Tuesday, March 19, 2013 11:46 AM
    Answerer
  • You can use OUTPUT  clause see BOL for more info 

    http://msdn.microsoft.com/en-gb/library/ms177564.aspx 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Tuesday, March 19, 2013 11:49 AM
    Tuesday, March 19, 2013 11:48 AM