copy column value from one database.table to anoth

问题:

Let's keep it short and sweet

I want to do this (I've failed numerous times now, and one attempt even updated the Row with blanks):

UPDATE Database2.Table1 
SET (Database2.Table1.Column1, Database2.Table1.Column2, Database2.Table1.Column3) 
VALUES 
   (Database1.Table1.Column1, Database1.Table1.Column2, Database1.Table1.Column3)
WHERE Database2.Table1.Column1 = Database1.Table1.Column1

These two already contain the same value, but the other 2 columns do not, and that is what I wish to change with this query..

Here's how each table look:

Database1.Table1

[id]
[name]
[applicationdate]
[startdate]
[shortdescription]
[longdescription]
[displayimg]
[contact]
[website]
[created]
[urlbase]
[site]
[keywords]
[type]
[location]

Database2.Table1

[id]
[name]
[applicationdate]
[startdate]
[content]
[keywords]
[customerid]
[urlbase]
[shortdescription]
[meta]
[type]
[site]
[searchurlbase]
[lang]
[educationlength]
[locations]
[educationwebsite]
[contact]
[tags]
[educationtypes]
[created]
[category]

Any help is greatly appreciated, thank you for your time :)

Sorry if it doesn't make any sense, I easily get confused when trying to explain problems I'm having

回答1:

UPDATE dest
  SET column2 = src.column2,
      column3 = src.column3
FROM Database2.dbo.Table1 AS dest 
INNER JOIN Database1.dbo.Table1 AS src
ON dest.column1 = src.column1;

Given your collate problem, you can specify collate in the equality operation. Having no idea which side is giving the problem, and assuming you don't care about case sensitivity:

UPDATE dest
  SET column2 = src.column2,
      column3 = src.column3
FROM Database2.dbo.Table1 AS dest 
INNER JOIN Database1.dbo.Table1 AS src
ON dest.column1 COLLATE Finnish_Swedish_CI_AS
 = src.column1  COLLATE Finnish_Swedish_CI_AS;

If you care about case sensitivity, then change both clauses to the _CS_AS_ one.

回答2:

It is too easy in mysql, if your databases in same server. The mysql command is: insert into db2.table_name(column_name you want) select from table1_name.coumn_name you want from table1

For example, insert into db2.new(id,name,email) select from register.id,register.name,register.email from register.

Where, db2 is 2nd db new is table in db2 register is in db1 where you copy data from this db to db