Transfer column data from one database to another
I'm trying to transfer an entire column's worth of data from the backup database to the current production database (earlier in the day I had ruined this column in production with a bad update). I am using MS SQL Server 2005.
In this example, I am trying to restore 'Column1' from DB2.Table1 into DB1.Table1:
begin transaction
update [DB1].[dbo].[Table1]
set [DB1].[dbo].[Table1].[Column1] = [DB2].[dbo].[Table1].[Column1]
from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias
where db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId
commit transaction
For me this query returns:
The multi-part identifier "DB2.dbo.Table1.Column1" could not be bound.
Any help would be appreciated.
Thanks!
EDIT:
Thanks to SQL Menace I got this query running. Thanks! See below for fixed query
begin transaction
update db1Alias
set db1Alias.[Column1] = db2Alias.[Column1]
from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias
where db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId
commit transaction
T开发者_JAVA技巧he problem was that I was not using my own declared alias' in my update and set statements. I didn't know you were supposed to use alias' before they were even declared.
Assuming that column1 is the real name of the column then the problem could be that you used an alias in the select but not in the update
here is what it should look like...I also used a new style JOIN
update db1Alias
set db1Alias.[Column1] = db2Alias.[Column1]
from [DB1].[dbo].[Table1] db1Alias
JOIN [DB2].[dbo].[Table1] db2Alias ON db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId
Here is an example you can run
first run this to create these 2 tables
use tempdb
go
create table BlaTest(id int)
insert BlaTest values(1)
go
create table BlaTest2(id int)
insert BlaTest2 values(1)
go
Now when you try to do this
update tempdb.dbo.BlaTest
set tempdb.dbo.BlaTest.id =tempdb.dbo.BlaTest2.id
from tempdb.dbo.BlaTest b
join tempdb.dbo.BlaTest2 a on b.id =a.id
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.BlaTest2.id" could not be bound.
But if you use the alias...no problem
update b
set b.id =a.id
from tempdb.dbo.BlaTest b
join tempdb.dbo.BlaTest2 a on b.id =a.id
Maybe you should create the column?
enter the following:
select * from information_schema.columns
where table_name = 'Table1'
do you see a column named column1?
update [DB1].[dbo].[Table1]
set [DB1].[dbo].[Table1].[Column1] = db2Alias.[Column1]
from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias
where db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId
The better would be
update [DB1].[dbo].[Table1]
set [DB1].[dbo].[Table1].[Column1] = db2Alias.[Column1]
from [DB1].[dbo].[Table1] db1Alias INNER JOIN [DB2].[dbo].[Table1] db2Alias
ON db1Alias.TeamId = db2Alias.TeamId
and db1Alias.IndividualId = db2Alias.IndividualId
精彩评论