copy data from one database to another

Hi,
In our build process (where SQLServer is used as an embedded database) we always
recreate the database from scratch. When the database is recreated, the script
also recreates some basic data from the script.
Some of the QA folks are having a problem with this. They are not willing to lose all
of their existing data when a new build is invoked. I am trying to come with a simple
solution.
1. thr EM user will rename current database they have.
ALTER DATABASE OldName
MODIFY NAME = NewName
2. the build process will recreate the new database.
3. Now I need a tool to copy data from the old database. Since both old and new database
will
be in the same instance, a simple INSERT INTO TABLE(col1,col2,col3) select
col1,col2,col3
from olddatabase:table will do. What the tool should do is to disregard any dropped
columns
from being copied and set any new columns added in the newdatabase to null.
To perform step 3 is there a tool already available. I have a vague idea on how to write a
stored procedure for this, but I would prefer a tool already existing. Why reinvent the
wheel?
TIAI think the easiest way to go about this is to use DTS. You can create a DTS
script that will do a straight copy from one database to another, specifying
which columns to skip.|||"mmm" <mmm@.discussions.microsoft.com> wrote
>I think the easiest way to go about this is to use DTS. You can create a DTS
> script that will do a straight copy from one database to another, specifying
> which columns to skip.
The columns to skip should be automatic. I don't want to be editing DTS every
time.
I am thinking of writing a stored procedure in master database
which will use the following logic.
1. It will loop around every table of the target database where the data will go to.
2. For each table it will find out the columns and build a string as follows
INSERT INTO TARGET_DB.TABLE(COL LIST) SELECT COL_LIST
FROM SOURCE_DB.TABLE
Next the problem to tackle is the order of table. While poplating the tables
in step (1), it should populate all those tables first which either have no child tables
or child tables. That is, the sequence of loading the tables should not violate
PKY and FKY relationship.
Anyone knows a way to query tables in a database in required order mentioned above.
TIA