October 9, 2008 at 8:14 am
,m
October 9, 2008 at 8:46 am
Are you copying all of the data from one database to another, or just a few tables?
If it's every table you could just take a backup of db1 and restore it to db2. Or if you just want data and not schema you could use select into like you've done in your procedure, but you could use the sp_msforeachtable undocumented system stored procedure. Here's a link to some info on it http://www.databasejournal.com/features/mssql/article.php/1490661.
If it's only selected tables and that list of tables will not change, it might be a good idea to write it out specifing the particular tables you want to move.
Another way that may be even easier would be to create a DTS Package that includes a Copy SQL Server Objects Task. should be pretty much point and click and run.
-Luke.
October 10, 2008 at 3:33 am
thanx, it was very helpfull.
But the problems never stop.
So I have one more.
October 10, 2008 at 4:38 am
October 10, 2008 at 4:57 am
thanx
October 10, 2008 at 6:12 am
For that many tables across that many databases, it might not be a bad idea to handle this with either a DTS (sql 2000) or SSIS (2k5+) task.
I think you'll find the greater degrees of flexibility those platforms offer you will be worthwhile.
-Luke.
October 10, 2008 at 7:45 am
You could create a table that has a list of your databases, then query that table and use the results from that query to populate a cursor to loop through each database. Depending on the size of your data, this could take a while. In a single script, I can't think of a way to get all of the transfers happening at the same time, even if you'd hard code it.
The major benefits of DTS are logging and such, plus you'd be able to begin numerous copies at the same time. I'm not really sure what the difference between one package and one script is, but if that's what your constraints are, then I suppose that's what you need to stick with.
Since this is all happening in one script it would probably be a good idea to put some logging and error handling code into your script. You never know when the connection to 1 of those 16 databases might fail. If it's db2, depending on how your script is written, it would error at that point and not update the other 15. Also, if it's db5 then you have some that are updated and some that aren't, which could make for a sticky situation.
-Luke.
October 10, 2008 at 8:07 am
thanxs
I was thinking to avoid cursor because of prefomance problems.
Do you have some suggestion.
October 10, 2008 at 8:15 am
Cursors cause performance issues because of looping executing things 1 at a time and not using set based operations. I am unaware of a set based operation that will copy tables to 16 different destinations at the same exact time.
Because this is supposed to happen all in the same script AFAIK there is no way to execute more than 1 statement at a time, as each will wait on the previous one. So why not make your life a bit easier and wrap it in a cursor so that you can keep it dynamic for when the destination sources are changed, because you know that will happen eventually.
Would you rather just add/update a record in a table or modify an entire procedure?
-Luke.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply