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

本文介绍了将列数据从一个数据库传输到另一个数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试将整个列中的数据从备份数据库转移到当前生产数据库中（那天早些时候，我在生产中损坏了该列，但更新不正确）。我正在使用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