One of the best ways to improve your skills is by helping other people in forums and newsgroups. I was doing just that tonight and I stumbled on this piece of code here: http://stackoverflow.com/questions/3622685/transfer-column-data-from-one-database-to-another
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
Can you tell what is wrong with the code? If you try to run that you will get the following error
The problem is that aliases are defined for the tables but not used in the column part.
Let’s take a closer look with some code that you can actually run. First create these two 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 run this piece of code, which is the same as the code at the beginning of the post except for the object names, you will get an error.
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
Here is the error
So what can be done?
Here is my preferred way of running this query, use the table aliases in the update and the columns
update b set b.id =a.id from tempdb.dbo.BlaTest b JOIN tempdb.dbo.BlaTest2 a on b.id =a.id
But you can also write the query like this by using the alias only for the column that is not being updated.
update tempdb.dbo.BlaTest set tempdb.dbo.BlaTest.id =a.id from tempdb.dbo.BlaTest b JOIN tempdb.dbo.BlaTest2 a on b.id =a.id
The important thing to remember is that you have to use the alias in the table that you are not updating, to be safe just use the alias all over, that way you don’t have to think whether to use the alias or not.