I have 2 databases and each of them has different tables. I want to copy all of tables of database A to database B while keeping the existing tables of database B.
What is the best solution?
Thanksuse DTS (Data Transformation Services), it's in sql server.|||Thanks for help.
DTS is easy to use. I selected the 3rd radiobutton "copy objects and data" to copy tables from DB A to B.
If I have the same table name in 5 DB - A, B, C, D, E,
A is the central DB,
the data of those tables in B to E will be copied to A's table daily,
can the index be auto-generated and appended to A's table?
e.g. The index in DB A's table are
1
2
3
4
5
I add 4 rows of DB B's table data to A.
Will the index in DB A's table like:
1
2
3
4
5
6
7
8
9
where 6-9 come from DB B.
I saw that there is an overwrite or append data options in DTS. Do I just choose 'Append' and it will do that?
If I set the schedule, can it be done daily?
Thanks for advice.|||it really depends on how tables B, C, D, E are set up, if you are meaning index=primary key, then you'll probably need to run your own insert statements to insert into table A, so they will get new primary keys, unless you want to run the risk of having duplicate primary keys inserted, which then, they really are usless and aren't primary keys. Then you have to wonder about other tables that are linked to these tables based on that primary key, do you now have to update them as well? yuck. Don't know your data requirments here, but having 5 tables all hold the same thing is usually not a good idea.
DTS can be scheduled easily, the easiest way I know how is to right-click on the package in the DTS view in enterprise manager, and select schedule package.
