Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-11-08 : 07:48:09
|
| Matt writes "Server 2000 and Windows XPI want to move about 40 tables from one database to another. I know you can use 'copy sgl server objects' and I know i can write a spored proc that contains the names of all the table I want to copy, but what I want is a little more adjustable.The tables I want to move and the source and target databases are variable, sometimes I'll move certain tables from databaseA to DatabaseB and sometimes it might be different tables from DatabaseB to DatabaseC. I'll be changing the table list and location about one per month. Ideally I'm thinking along the lines of a stored proc that is parametised, I supply the souce and destination databases as well as the location of the text file containing a list of tables. I press go and the tables are copied over.Is that challenging? (It is for me)Thanks in advanceMatt Frost" |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-11-08 : 08:34:07
|
| If the database are on same box then you can you can use normal T-SQL statement to transfer tables from one database to other.Select * into <Destination Database>.<TableName> from <Source Database>.<TableName>As you mentioned above that this is dynamic, you can make these strings and execute them using exec or sp_executeSQL.If the database is not on the same server then u can add a linked server and then execute the the same query with the name of the linked server.RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-08 : 08:39:27
|
| Data Tranformation Service (DTS) Is the tool which is mainly used for migrating the data from on database to another. Import Export is also on the features avilable in EM which can be used for the same.. Complicated things can be done by simple thinking |
|
|
|
|
|
|
moving tables with dts