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 |
|
josmond
Starting Member
3 Posts |
 Posted - 2002-07-01 : 11:07:36
|
| Hi,I'm using SQL 2000 with a Access ADP front end. It might sound bizarre but I'm writing an upgrade routine which merges two SQL Databases into one. I need to copy a table from one SQL Database ('cat1') to another ('cat2') - I want to be able to do this in access vb script because the upgrade is in steps Is there anyway of achieving this??? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-01 : 11:17:27
|
| presumeing the tables in both databases are the same structure...then insert into server1.owner1.database1.table1select * from server2.owner2.database2.table2should point you in the right direction....providedthat server1 + server2 can see (are registeredon ) each othersearch here for "linked servers"....suitable advice should be around here arleady. |
 |
|
|
josmond
Starting Member
3 Posts |
Posted - 2002-07-01 : 11:28:02
|
| Thanks for yur input - but the table that needs to be copied doesn't exists in the target database - hmmm just like a 'copy sql object' function...Anybody??? |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-01 : 11:36:05
|
| Use "Generate SQL Scripts" under EM to create a script file to recreate the object including any constraints established for the objects. Following that use Andrews advice to copy data bearing in mind you would be impacting any network the data is sent across.Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-01 : 11:39:47
|
quote:
Thanks for yur input - but the table that needs to be copied doesn't exists in the target database - hmmm just like a 'copy sql object' function...
DTS has such a operator. I would particularly recommend DTS because of your earlier comment quote:
because the upgrade is in steps
, as DTS also provides for complex workflows in data manipulations.Jonathan Boott, MCDBA |
|
|
|
josmond
Starting Member
3 Posts |
Posted - 2002-07-01 : 11:49:47
|
| Cool. I have created a DTS Package that copies a table form 1 db to another, and can execute it from Enterprise Manager. Is there a way of executing this from the Access ADP??? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-01 : 14:03:39
|
quote:
Is there a way of executing this from the Access ADP???
You have a few options. If you can run shell commands, you can execute dtsrun.exe and specify the appropriate command line switches. You could also schedule the job and run sp_startjob.Jonathan Boott, MCDBA |
|
|
|
|
|
|
Copying Tables