Transfer column data from one database to another

Question

Hello,

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

The 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.

Answer 1

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?

Answer 2

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

Answer 3

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