CODEWITHSUNDEEP
c#sql-serverdataadapter
1Mayur
1Mayur

Reputation: 3485

Moving Data from one table to another

Hie frnds,

I have two tables say "Orders" and "OrdersXML"

now I wish to remove orders which are more than 7 days old to OrdersXML table which i have done using sql adapter update function over dataset, which do this job taking 100 rows at a time. I want to delete the rows from the orders table which have been moved to OrdersXML table How can i Achieve this?? I want to ensure that a row in Orders is only deleted after it has been inserted into OrdersXML. I don't want to loose any of the data.. not even by accident..

Should I use trigger? or should i code it in C# itself?

as m using data-adapter I cant get the ids which have been inserted if any exception comes in between.. can i??

Upvotes: 0

Views: 3009

Answers (3)

KM.
KM.

Reputation: 103607

use DELETE with an OUTPUT clause and do it in one statement:

DECLARE @OldTable table(col1 int, col2    varchar(5), col3 char(5), col4     datetime)
DECLARE @NewTable table(col1 int, column2 varchar(5), col3 int    , col_date char(23), extravalue int, othervalue varchar(5))
INSERT @OldTable VALUES (1 , 'AAA' ,'A'  ,'1/1/2010'           )
INSERT @OldTable VALUES (2 , 'BBB' ,'12' ,'2010-02-02 10:11:22')
INSERT @OldTable VALUES (3 , 'CCC' ,null ,null                 )
INSERT @OldTable VALUES (4 , 'B'   ,'bb' ,'2010-03-02'         )

DELETE /*top (1000)*/ @OldTable
    OUTPUT DELETED.col1
          ,DELETED.col2
          ,CASE
               WHEN ISNUMERIC(DELETED.col3)=1 THEN DELETED.col3 
               ELSE NULL END
          ,DELETED.col4
          ,CONVERT(varchar(5),DELETED.col1)+'!!'
        INTO @NewTable (col1, column2, col3, col_date, othervalue)
    OUTPUT 'Rows Deleted: ', DELETED.* --this line returns a result set shown in the OUTPUT below
    WHERE col1 IN (2,4)

SELECT * FROM @NewTable

output:

               col1        col2  col3  col4
-------------- ----------- ----- ----- -----------------------
Rows Deleted:  2           BBB   12    2010-02-02 10:11:22.000
Rows Deleted:  4           B     bb    2010-03-02 00:00:00.000

(2 row(s) affected)

col1        column2 col3        col_date                extravalue  othervalue
----------- ------- ----------- ----------------------- ----------- ----------
2           BBB     12          Feb  2 2010 10:11AM     NULL        2!!
4           B       NULL        Mar  2 2010 12:00AM     NULL        4!!

(2 row(s) affected)

You can use the TOP (...) to limit it as necessary.

Upvotes: 2

Ryan
Ryan

Reputation: 8005

If you want to write the SQL with a script, use a SqlCommand with a SQL transaction:

BEGIN TRANSACTION

-- Copy rows from Orders to OrdersXML

-- Delete rows from Orders that were copied

COMMIT TRANSACTION

If you want to do this with objects and code, use a SqlTransaction object:

// code sample adapted from MSDN
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
    SqlCommand command = connection.CreateCommand();
    command.Transaction = transaction;

    try
    {
        command.CommandText = "TODO"; // Copy rows from Orders to OrdersXML
        command.ExecuteNonQuery();
        command.CommandText = "TODO"; // Delete copied rows from Orders
        command.ExecuteNonQuery();

        // Attempt to commit the transaction.
        transaction.Commit();
    }
    catch (Exception ex)
    {
        try
        {
            // Attempt to roll back the transaction.
            transaction.Rollback();
        }
        catch (Exception ex2)
        {
            // This catch block will handle any errors that may have occurred
            // on the server that would cause the rollback to fail, such as
            // a closed connection.
        }
    }

Upvotes: 2

KingCronus
KingCronus

Reputation: 4519

I would personally recommend writing a stored procedure, so that you don't have the latency of using a C# client. You can then write a script to call this stored procedure every day or whatever.

Look up "Transactions", you can make it so that if one part of the query fails (i.e. the insert), then the rest of the query rolls back to the previous good state.

Upvotes: 1

Related Questions