I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences
Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.
Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.
In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.
I just went back and re-read my original post and I see where I need to clarify a few points.
I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.
Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better!
So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.
it will also script all the data out (optionally) using bcp.exe.
I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.