sql query to export row values from one SQL Server database to another

Question

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?

Answer 1

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.

Answer 2

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