0

I need to move a series of tables from one datasource to another. Our hosting company doesn't give shared passwords amongst the databases so I can't write a SQL script to handle it.

The best option is just writing a little coldfusion scripty that takes care of it.

Ordinarily I would do something like: SELECT * INTO database.table FROM database.table

The only problem with this is that cfquery's don't allow you to use two datasources in the same query.

I don't think I could use a QoQ's either because you can't tell it to use the second datasource, but to have a dbType of 'Query'.

Can anyone think of any intelligent ways of getting this done? Or is the only option to just loop over each line in the first query adding them individually to the second?

My problem with that is that it will take much longer. We have a lot of tables to move.

Rumpleteaser
  • 4,142
  • 6
  • 39
  • 52
  • how about generate the data as CSV, either write it to file and download that, or cfcontent it and download it? – Henry May 10 '12 at 00:10
  • Then I'd have to manually upload it too. I'm looking for a quick dynamic way. – Rumpleteaser May 10 '12 at 00:12
  • I think it'd be safest, if not fastest. – Henry May 10 '12 at 02:03
  • if you have linked servers that's by far the fastest. Use one server as your datasource and reference the other in your from statement using `servername.databasename.dbo.tablename` – Matt Busche May 10 '12 at 12:08

4 Answers4

5

Ok, so you don't have a shared password between the databases, but you do seem to have the passwords for each individual database (since you have datasources set up). So, can you create a linked server definition from database 1 to database 2? User credentials can be saved against the linked server, so they don't have to be the same as the source DB. Once that's set up, you can definitely move data between the two DBs.

We use this all the time to sync data from our live database into our test environment. I can provide more specific SQL if this would work for you.

Bill Moniz
  • 311
  • 1
  • 3
  • That solves my problem thanks. And I found a tutorial here: http://sqltutorials.blogspot.co.nz/2009/08/linked-servers.html – Rumpleteaser May 11 '12 at 01:52
3

You CAN access two databases, but not two datasources in the same query.

Evik James
  • 10,335
  • 18
  • 71
  • 122
  • If the username the datasource is created under has permission to both tables you can infact reference 2 datasources in the same cfquery. – Paul May 10 '12 at 22:58
  • @Paul, about a year ago, I had the same question as the original post. – Evik James May 11 '12 at 12:36
3

I wrote something a few years ago called "DataSynch" for just this sort of thing.

http://www.bryantwebconsulting.com/blog/index.cfm/2006/9/20/database_synchronization

Everything you need for this to work is included in my free "com.sebtools" package: http://sebtools.riaforge.org/

I haven't actually used this in a few years, but I can't think of any reason why it wouldn't still work.

Steve Bryant
  • 1,046
  • 5
  • 7
0

Henry - why do any of this? Why not just use SQL manager to move over the selected tables usign the "import data" function? (right click on your dB and choose "import" - then use the native client and permissions for the "other" database to specify the tables. Your SQL manager will need to have access to both DBs, but the db servers themselves do not need access to each other. Your manager studio will serve as a conduit.

Mark A Kruger
  • 7,183
  • 20
  • 21
  • Again ... you need permissions to both DBs (separate permissions is fine - they are entered at the time of the import). – Mark A Kruger May 10 '12 at 02:42