tags:

views:

252

answers:

2

I have a table with columns 'id', 'name', 'value', 'uniqueConst' in both databases. Now i need to export only all 'value' fields from one database to another 'where db1.uniqueConst = db2.uniqueConst'. Other fields are already in db2 and 'id's in both db are identity, so i cant just export the whole table. Can you help me?

+1  A: 

You can do something like this:

USE d2
UPDATE tablename
SET value = d1table.value
FROM tablename d2table INNER JOIN db1.schemaname.tablename d1table
ON d2table.uniqueConst = d1table.uniqueConst

...as long as you have permissions on both databases and tables, and as long as the uniqueConst field is unique, as its name suggests :)

edit: I'm assuming the databases are on the same instance; if they're not, you'll need to add a linked server, and use four-part notation to include the instance names.

Jeremy Smyth
+1  A: 

In MS SQL Server you can setup linkedservers. From there its just a straight update

update 
   [databaseB].dbo.[tableb]
set 
[databaseB].dbo.[tableb].value = [databaseA].dbo.[tablea].Value
From
[databaseA].dbo.[tablea]
WHERE 
  [databaseA].dbo.[tablea].uniqueConst =   [databaseb].dbo.[tableb].uniqueConst
John Nolan
i think after FROM should be [databaseA].dbo.[tablea] not [databaseA].dbo.[tablea].Valuebut your solution works so thanks.
Tamm
thanks updated it as advised
John Nolan