SQL 2005 copy single column between databases

Question

I'm still fairly new to T-SQL and SQL 2005. I need to import a column of integers from a table in database1 to a identical table (only missing the column I need) in database2. Both are sql 2005 databases. I've tried the built in import command in Server Management Studio but it's forcing me to copy the entire table. This causes errors due to constraints and 'read-only' columns (whatever 'read-only' means in sql2005). I just want to grab a single column and copy it to a table.

There must be a simple way of doing this. Something like:

INSERT INTO database1.myTable columnINeed
SELECT columnINeed from database2.myTable

Answer 1

Inserting won't do it since it'll attempt to insert new rows at the end of the table. What it sounds like your trying to do is add a column to the end of existing rows.

I'm not sure if the syntax is exactly right but, if I understood you then this will do what you're after.

1. Create the column allowing nulls in database2.

2. Perform an update:

   UPDATE database2.dbo.tablename SET database2.dbo.tablename.colname = database1.dbo.tablename.colname FROM database2.dbo.tablename INNER JOIN database1.dbo.tablename ON database2.dbo.tablename.keycol = database1.dbo.tablename.keycol