将列数据从一个数据库传输到另一个数

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.

我正在尝试将整个列的数据从备份数据库传输到当前的生产数据库(当天早些时候我在生产中破坏了这个列并且更新了一次)。我正在使用MS SQL Server 2005。

In this example, I am trying to restore 'Column1' from DB2.Table1 into DB1.Table1:

在这个例子中,我试图将DB2.Table1中的“Column1”恢复为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.

无法绑定多部分标识符“DB2.dbo.Table1.Column1”。

Any help would be appreciated.

任何帮助,将不胜感激。

Thanks!

EDIT:

Thanks to SQL Menace I got this query running. Thanks! See below for fixed query

感谢SQL Menace我运行了这个查询。谢谢!请参阅下面的固定查询

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.

问题是我在更新和设置语句中没有使用我自己声明的别名。在他们被宣布之前,我不知道你应该使用别名。

3 个解决方案

#1

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

假设column1是列的真实名称,那么问题可能是您在select中使用了别名但在更新中没有

here is what it should look like...I also used a new style JOIN

它应该是什么样子......我还使用了新的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.

消息4104,级别16,状态1,行2无法绑定多部分标识符“tempdb.dbo.BlaTest2.id”。

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

#2

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?

你看到一个名为column1的列吗?

#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

#1