April 14, 2008 at 3:35 pm
What is the best way to move data that is in one database into another database within SQL Server?
I have an old SQL DB both in SS2000 and I want to move the information from the older DB (all the table names are the same) to the newer one. The new one is blank. The old one has all my data in it.
Thanks!
April 14, 2008 at 3:41 pm
I'd use DTS. The Import/Export Wizard would work fine for this.
Greg
April 14, 2008 at 4:07 pm
The best i can find is how to Import and Export information from Access to SS2000. Is this the same technique?
April 14, 2008 at 5:25 pm
Yes, that's the tool. Just use SQL Server as both the source and destination. BooksOnLine has more information and there are lots and articles on this site with examples.
Greg
April 15, 2008 at 7:48 am
You can also backup the old database and restore it to the new database. Depending on the volume of data, this may be quicker. Just curious, why did you create a blank database if it's identical to the old one?
-- You can't be late until you show up.
April 15, 2008 at 7:57 am
I updated the new database structure slightly and didn't want to go back and figure out how to modify the existing one. It would probaby have been the best way just to modify the existing one but i didn't want to take any chances. I mean the modifications weren't much just add another field here or there for a Revised Date Or add a trigger here or there.
Since the DB was not yet operational i thought, why not just do it over and move the data.
Thanks!
April 15, 2008 at 8:11 am
Another just curious: How do you plan on promoting your changes to your structures back to the production database?
Based on your original post, DTS, as Greg stated, should work well for you. Good luck!
-- You can't be late until you show up.
April 15, 2008 at 8:17 am
I'm sorry I'm not familar with the term "promoting."
April 15, 2008 at 8:28 am
Promoting (my definition, anyway): applying the changes you made in the new database to the old. Or will the new database ultimately be your new production database?
Everyone has their own method for development work. My method (which works for ME):
1) Do modifications in dev database (saving scripts) - developer (sometimes me, sometimes not)
2) Unit test changes - developer
3) (promoting) Apply scripts to a test environment - developer
4) User testing - end users
5) (promoting) Apply scripts to production environment - DBA (me)
-- You can't be late until you show up.
April 15, 2008 at 8:55 am
Thanks for the explanation. The new one is the production one. The old one is a test database. The changes are not super significant. I have a script that i wrote in T-SQL and i just updated the original script. I thought it would be easier to reference and to be sure that everything was the way i wanted it if i just used one script rather than the old one and then add-ons or improvements (amendments).
I'm a so this feels better. It doesn't feel like a patched garment. I'm sure that would not be the case but it just feels better and i don't have to study different scripts to figure out, "Why did i do that."
Is there a way to make this as resolved?
Thanks
April 15, 2008 at 9:10 am
Not sure if WE can close a topic (I believe that's what you're asking). I know Steve can. Simply quit posting to it and it'll fall off everyone's radar.
-- You can't be late until you show up.
April 21, 2008 at 1:26 pm
[font="Arial"]
Hello,
If the two data bases are both MSSQL 2000, just add the destination server as a linked server.
Then you can do distributed transaction processing between the two servers and or dot notation operations.
As said above, DTS is a effective solution to moving data between servers too.
They both work well.
Regards,
Terry
[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply