Migrating Data From one table in DB to Another table in another DB
Hello, I have around 10 tables that contain data that need to be moved from these tables to another DB containing the same tables (this is an archiving process that i am using to archive old data based on a specific condition) in the past i created a stored procedure that uses (insert into select from) statement in order to move the data and then i deleted the data from the original table, but this procedure is taking too long to execute (i need to run it every 2 minutes) Since i am using the SQL Server 2008 Enterprise edition i was wondering if someone could help me find a more advanced way to solve my problem (Replication maybe?...) espescially that i am not aware of a tool in SQL server 2008 that does this kind of work. Thank you in advance
I don't think you'll find anything that is a lot quicker than what you're doing. You might be able to do INSERT INTO ... DELETE FROM with OUTPUT clause, but I would imagine that the speed difference wouldn't be huge...
you can create script by rt clicking on the database and utilise it or else you can have a backup preserving replication setting 3. have a script of you repliation, backup and restore the database, delete oprhan users ,then run the scrip which creates replication
I agree with Matt (+1), what you are describing is probably nearly the best way. You could certainly try SSIS to see if that would be faster, but when dealing with moving data between two SQL Servers I *generally* find SSIS to be no faster than T-SQL and sometimes slower. Now, on a higher level, perhaps you can relook at the actual requirements and see if you really need to do it this way. If you just need to have the data on the destination server without it being deleted from the original than transactional replication may be the way to go. It tends to be reasonably fast and stable once set up properly and stays out of the end users way quite well.