0

Possible Duplicate:
update one table with data from another

Extreme SQL noob here. I have two databases (Database1, Database2). Each db has the same tables. I want to update TableA in Database2 with the data in TableA from Database1 (Database1,TableA -> Database2,TableA).

What's the best way to do this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Keith
  • 1,959
  • 10
  • 35
  • 46
  • 2
    Which RDBMS are you using (MS SQL, Oracle, MySQL)? Do you want to completely overwrite the destination table with the source table? Just add new rows? – Tom H Sep 15 '11 at 16:52
  • This is in MS SQL. Basically the destination table contains much of the same data as the source table, but the source table may have different values in existing rows and/or have new rows. I want the destination table to match the source table, so I may have to insert new rows if they do not exist in the destination table, and also update existing rows. – Keith Sep 15 '11 at 17:53
  • How often do you need to sync the 2 tables? Are you looking for real-time or something on a daily basis? – Swoop Sep 15 '11 at 17:57
  • This will probably just be a one-time update. – Keith Sep 15 '11 at 18:00

2 Answers2

2

Like this, assuming you use a dbo schema.

insert into Database2.dbo.TableA (column1, column2, etc)
select column1, column2, etc
from Database1.dbo.TableA

You might want to truncate Database2.dbo.TableA first if the incoming data should overwrite the old or you are using an identity field.

If you need the ids to be the exact same across the tables, you should disable the identity property on Database2.dbo.Table before running the script and re-enable it afterward.

Devin Burke
  • 13,642
  • 12
  • 55
  • 82
1

If you are doing this as a one-time data sync, SQL Server offers an Import/Export Data under the Tasks option when you right-click on a particular database. You can select the sql server/database/tables to be used as a data source and destination and then run the job. When you set this up, there will be an opportunity to set an option to clear the destination table and insert all the data from the source.

You can also schedule these types of transactions to be run on a scheduled basis, though I have never done much work in this area.

Swoop
  • 1,423
  • 4
  • 18
  • 34
  • Tried the Import/Export method but it failed on the copy process. The error message was very cryptic and not very useful. Is there a way to script this? I also tried Justin's solution above but it failed to insert do to duplicate keys. I just want to insert new rows/keys and update existing ones if they are different. – Keith Sep 15 '11 at 18:33
  • Can you post the error message? You could use Justin's approach if you clear all the records from your destination table first. That should avoid the duplicate keys. To clear your records, I have used truncate table which worked more efficiently for me. – Swoop Sep 15 '11 at 18:51
  • Thanks. I'll try "truncate" and then try running Justin's approach. – Keith Sep 15 '11 at 19:08
  • Here is the error message (there was lots more, but here is the gist): `"Violation of PRIMARY KEY constraint 'PK_SharedContent_sharedContentGuid'. Cannot insert duplicate key in object 'dbo.SharedContent'.". (SQL Server Import and Export Wizard)`. Guess I just need to clear the destination table first. :-) – Keith Sep 15 '11 at 19:13
  • Looks like both problems are related to clearing out the old data to avoid duplicate keys. – Swoop Sep 15 '11 at 19:22
  • Tried to truncate like so: `TRUNCATE TABLE tablename`. Got the following error message: `Cannot truncate table because it is being referenced by a FOREIGN KEY constraint.` – Keith Sep 15 '11 at 19:25
  • I have never ran into the problem with the Foreign key constraint because of the nature of the tables I did the data transfers on. You might be able to disable the foreign key constraint temporarily to do this data transfer. I would assume you will want to do a data transfer for each table involved in the foreign key constraint to keep your db structure working correctly. – Swoop Sep 15 '11 at 19:31
  • Yeah, you are correct about disabling the foreign key constraint. More info is found here about this issue: http://forums.asp.net/t/1283840.aspx/1 – Keith Sep 15 '11 at 19:41