Should I create a stored procedure on the database being read from or the database being written to?-CodePudding

I'm creating a stored procedure (and a schedule/job to execute the procedure every 5 minutes) which will read data from a table in one database, and write data to another table in a different database.

Does it matter whether I create the stored procedure on the database being read from, or the database being written to?

EDIT: It's a small procedure which showed to have taken 0 seconds to perform.

CodePudding user response：

Without any special information, you could do any of the two. It would be called either "push" or "pull". One idea would be to put the burden of the proc on the database with least traffic.

Moving data between systems is usually done in ETL steps - Extract, Transform, Load. What makes it easier for my thinking usually is to export original data from source db to destination db, and then do both the processing and the final insertions on the target one. In this case, I'll put the spc on the destination db, since most things (except the first data transfer) happen there.

This is just a rule of thumb that has proved convenient over a few years. I am interested myself in other answers.

Last but not least, if there are no transformations needed, consider replication.

CodePudding user response：

I think the best practice is to create procedure in database where you are insert/update your data. Like so you can manage all your stored procedures easy. Like a source and target, better to store your code in target, so target will be independent. So I think it is more manageable to store your stored procedures in a target database.

CodePudding user response：

Neither.

An operational concern should be solved using an operational approach.

Neither database should "know" this operation is being performed - having operational code in the database increases testing complexitiy and moves the database closer to being a swiss army knife (which is bad).

Rather, an intermediate agent running separately from either database should control the operation.

Create a script to:

export the data from the source database to a file using database utilites
move the file to the target database
import the data fromt he file using database utilities

Schedule the script to run using crontab or whatever scheduling/workflow tool you choose (not using either database in any way to schedule).