How can I write a stored procedure to copy data from one database to another database on the same SQL Server, every weekend?

Tags:
SQL Server
Stored Procedures
How can I write a stored procedure to copy data from one database to another database on the same SQL Server, every weekend?

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is kind of a two part question. The first part being how to access data in another database, and the second part being how to schedule the procedure to run each week.

Part 1. In order to access data in another database you have to use the three part name of the object. With that format being Database.Owner.Object. So if you were going to create a procedure which truncated a table and then loaded in all the data from a table in another database it would look like this.

CREATE PROCEDURE YourProcedure AS
TRUNCATE TABLE YourTable

INSERT INTO YourTable
SELECT *
FROM OtherDatabase.dbo.SourceTable
GO

Part 2. In order to schedule the procedure to run each week you will want to use the the SQL Server Agent to schedule the job. In the User Interface navigate to the SQL Agent (in SQL 2000 it’s under management, in SQL 2005 it’s directly under the server). Right click on Jobs and select new job. Put the stored procedure name on the job step, and setup the schedule to run once per week.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following