SQL 2005 copy single column between databases

Question

Hi, 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

insert into Test2.dbo.MyTable (MyValue) select MyValue from Test1.dbo.MyTable

This is assuming a great deal. First that the destination database is empty. Second that the other columns are nullable. You may need an update instead. To do that you will need to have a common key.

Answer 2

There is a simple way very much like this as long as both databases are on the same server. The fully qualified name is dbname.owner.table - normally the owner is dbo and there is a shortcut for ".dbo." which is "..", so...

INSERT INTO Datbase1..MyTable
    (ColumnList)
SELECT FieldsIWant
  FROM Database2..MyTable

Answer 3

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

Answer 4

You could also use a cursor. Assuming you want to iterate all the records in the first table and populate the second table with new rows then something like this would be the way to go:

DECLARE @FirstField nvarchar(100)

DECLARE ACursor CURSOR FOR
SELECT FirstField FROM FirstTable 

OPEN ACursor
FETCH NEXT FROM ACursor INTO @FirstField
WHILE @@FETCH_STATUS = 0
BEGIN

   INSERT INTO SecondTable ( SecondField ) VALUES ( @FirstField )

   FETCH NEXT FROM ACursor INTO @FirstField

END

CLOSE ACursor   
DEALLOCATE ACursor

Answer 5

first create the column if it doesn't exist:

ALTER TABLE database2..targetTable
ADD targetColumn int null -- or whatever column definition is needed

and since you're using Sql Server 2005 you can use the new MERGE statement. The MERGE statement has the advantage of being able to treat all situations in one statement like missing rows from source (can do inserts), missing rows from destination (can do deletes), matching rows (can do updates), and everything is done atomically in a single transaction. Example:

MERGE database2..targetTable AS t
USING (SELECT sourceColumn FROM sourceDatabase1..sourceTable) as s
ON t.PrimaryKeyCol = s.PrimaryKeyCol -- or whatever the match should be bassed on
WHEN MATCHED THEN 
    UPDATE SET t.targetColumn = s.sourceColumn
WHEN NOT MATCHED THEN
    INSERT (targetColumn, [other columns ...]) VALUES (s.sourceColumn, [other values ..])

The MERGE statement was introduced to solve cases like yours and I recommend using it, it's much more powerful than solutions using multiple sql batch statements that basically accomplish the same thing MERGE does in one statement without the added complexity.

Answer 6

MERGE is only available in SQL 2008 NOT SQL 2005