Move complete table to archive in MVC 4

Question

I am working on an MVC 4 project which requires me to move data from an active table to a table for archived content.

I understand the with Entity Framework, the tables are tightly bounded with the models. I have created two models - one for the active records and one for the archived records.

What is the best way to add all the data in active table to archive and remove all the contents in active table for fresh use?

P.S: I am a bit paranoid about the error tolerance here, as I may be dealing with around 30000 records at a time. I need to successfully move all records to archive and ensure deleting them only after successful copy.

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

Even though you are using Entity Framework, you can still use Store Procedures. This is a good case to use a stored procedure, as you can do a set based operation in the sproc (fast), rather than iterating through all of the records in code (slow).

Here are some steps for how to add the sproc to the EF (you could just Google this too): Adding stored procedures complex types in Entity Framework

Your sproc would probably look something like:

SET IDENTITY_INSERT ON dbo.ArchiveTable --Assuming you have an identity column
INSERT INTO dbo.ArchiveTable(
    Col1
   ,Col2
)
SELECT 
   Col1
   ,Col2
FROM dbo.MainTable

SET IDENTITY_INSERT OFF dbo.ArchiveTable --Assuming you have an identity column

DELETE * FROM dbo.MainTable

Wrap that in a transaction (to satisfy your error tolerance) and that should be a pretty quick execution for 30,000+ records. I would recommend that you return something like the number of records affected or something like that, all of which you should be able to return from the stored procedure.

edited May 23 '17 at 12:29

Community

1
1

answered Jan 17 '14 at 17:47

wilsjd

2,178
2
23
37

Works best. For now I am sending a custom query for INSERT INTO from the code itself. How would this affect the performance as opposed to a Stored Procedure? My query is the same as yours inside the stored procedure above. – Omkar Khair Jan 20 '14 at 12:38
1

@Omkar I don't know the performance benefits either way. In theory, I would think they'd be similar. I am not aware of how to do that in EF though, so maybe you are using a different kind of connection (e.g. ADO.NET) to send that query to the database? If that is the case, I would still suggest the stored procedure way to make your code more maintainable; meaning that whomever has to edit your application later on can rely on the EF being the only connection to the database. It's not a must, but I would expect it if it were my project. Otherwise, comment the heck out of it to make it clear. – wilsjd Jan 20 '14 at 16:26
1

Another thing to consider with having the INSERT INTO query in the code is that now your .NET code is now even more dependant on that schema. If the database schema changes, you'll have to update this part of the code as well as the EF. Whereas, if you stick it in a sproc, you just update the EF. – wilsjd Jan 20 '14 at 16:29

score 0 · Answer 2 · edited May 23 '17 at 11:57

Unless you have to, don't do it in code - wilsjd's answer is right on - create a transacted stored procedure that you call straight from EF.

But if you have a reason to do it in code - say because you don't have a good way to access both tables from within a stored procedure, just be sure to understand and do the right thing with the transaction from within your code. This is a good answer discussing this: Entity Framework - Using Transactions or SaveChanges(false) and AcceptAllChanges()

Move complete table to archive in MVC 4

2 Answers2