Skip to main content
A
Anonymous
November 15, 2006
Question

Moving tables between dbs as a scheduled event

  • November 15, 2006
  • 4 replies
  • 419 views
Basically, I need a current user table in two databases, without having to add new users to two separate dbs.

I'm trying to set up a scheduled event that will delete the User table from database A, and copy a table of the same name from database B into database A. I'm assuming that since I'm using two separate databases that I can't use a stored procedure.

Anyone have any suggestions? I tried to use a DTS wizard to copy the contents of dbA.table into dbB.table, but it vomited on a primary key error.

This topic has been closed for replies.

4 replies

A
Anonymous
November 15, 2006
Got it done! I did an import and choose "between two SQL databases," and that allowed me to import the keys along with the tables. I was able to schedule that to run every night at midnight, and in the even that someone can't wait that long, I can mess with them manually.

THANK YOU, guys. I'm a new CF developer, and I'm more than daunted by what I was left to work with.
D
Dan_Bracuk
Inspiring
November 15, 2006
Simpler, copy table from B to A with different name. Run two queries to update the real table in A. Then drop the table you just copied over.
A
Anonymous
November 15, 2006
Trevor -- I didn't drop and recreate the first time. The second time (just now, after your first post), I did drop and recreate -- only there are foreign key constraints. Is there a way to drop all FK constraints, drop table, recreate, and then recreate the FKs?
T
TSB
Inspiring
November 15, 2006
I have done this before in SQL Server 2000 Enterprise using database replication. You may want to google it to see if that would work for you.

Trevor
ColdFusion Tips
T
TSB
Inspiring
November 15, 2006
Also I don't see how it didn't work using DTS. The primary key should copy over fine as long as you drop and recreate the table before you start to copy the data over.

Trevor
Don't click here
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices