将列数据从一个数据库传输到另一个数据库 [英] Transfer column data from one database to another

问题描述

我正在尝试将整个列中的数据从备份数据库转移到当前生产数据库中（那天早些时候，我在生产中损坏了该列，但更新不正确）。我正在使用MS SQL Server 2005。

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.

在此示例中，我试图将'Column1'从DB2.Table1还原到DB1.Table1：

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:

部件标识符
DB2.dbo.Table1.Column1不能绑定
。

The multi-part identifier "DB2.dbo.Table1.Column1" could not be bound.

任何帮助都会

谢谢！

编辑：

由于SQL威胁，我使此查询运行。谢谢！参见下面的固定查询

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.

推荐答案

假定column1是真实名称该列的问题可能是您在选择中使用了别名，但未在更新中使用别名

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

这应该是它的样子...我也使用了新样式JOIN

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

现在尝试执行此操作

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

消息4104，级别16，状态1，第2行

不能绑定多部分标识符 tempdb.dbo.BlaTest2.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

这篇关于将列数据从一个数据库传输到另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！