<p>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.</p>

<p>There must be a simple way of doing this. Something like:</p>

<pre><code>INSERT INTO database1.myTable columnINeed
SELECT columnINeed from database2.myTable
</code></pre>

[{'answer_id': 52368, 'author': 'Craig', 'author_id': 2894, 'author_profile': 'https://Stackoverflow.com/users/2894', 'pm_score': 0, 'selected': False, 'text': '<p>insert into Test2.dbo.MyTable (MyValue) select MyValue from Test1.dbo.MyTable</p>\n\n<p>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.</p>\n'}, {'answer_id': 52376, 'author': 'Peter', 'author_id': 5189, 'author_profile': 'https://Stackoverflow.com/users/5189', 'pm_score': 2, 'selected': False, 'text': '<p>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...</p>\n\n<pre><code>INSERT INTO Datbase1..MyTable\n    (ColumnList)\nSELECT FieldsIWant\n  FROM Database2..MyTable\n</code></pre>\n'}, {'answer_id': 52389, 'author': 'Allain Lalonde', 'author_id': 2443, 'author_profile': 'https://Stackoverflow.com/users/2443', 'pm_score': 4, 'selected': True, 'text': "<p>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.</p>\n\n<p>I'm not sure if the syntax is exactly right but, if I understood you then this will do what you're after.</p>\n\n<ol>\n<li><p>Create the column allowing nulls in database2.</p></li>\n<li><p>Perform an update:</p>\n\n<p>UPDATE database2.dbo.tablename\nSET database2.dbo.tablename.colname = database1.dbo.tablename.colname\nFROM database2.dbo.tablename INNER JOIN database1.dbo.tablename ON database2.dbo.tablename.keycol = database1.dbo.tablename.keycol</p></li>\n</ol>\n"}, {'answer_id': 52405, 'author': 'Carl', 'author_id': 5449, 'author_profile': 'https://Stackoverflow.com/users/5449', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>DECLARE @FirstField nvarchar(100)\n\nDECLARE ACursor CURSOR FOR\nSELECT FirstField FROM FirstTable \n\nOPEN ACursor\nFETCH NEXT FROM ACursor INTO @FirstField\nWHILE @@FETCH_STATUS = 0\nBEGIN\n\n   INSERT INTO SecondTable ( SecondField ) VALUES ( @FirstField )\n\n   FETCH NEXT FROM ACursor INTO @FirstField\n\nEND\n\nCLOSE ACursor   \nDEALLOCATE ACursor\n</code></pre>\n'}, {'answer_id': 52421, 'author': 'Pop Catalin', 'author_id': 4685, 'author_profile': 'https://Stackoverflow.com/users/4685', 'pm_score': 2, 'selected': False, 'text': '<p>first create the column if it doesn\'t exist:</p>\n\n<pre><code>ALTER TABLE database2..targetTable\nADD targetColumn int null -- or whatever column definition is needed\n</code></pre>\n\n<p>and since you\'re using Sql Server 2005 you can use the new <a href="http://msdn.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow noreferrer">MERGE</a> statement.\n<strong>The <a href="http://msdn.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow noreferrer">MERGE</a> statement has the advantage of being able to treat all situations in one statement</strong> 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:</p>\n\n<pre><code>MERGE database2..targetTable AS t\nUSING (SELECT sourceColumn FROM sourceDatabase1..sourceTable) as s\nON t.PrimaryKeyCol = s.PrimaryKeyCol -- or whatever the match should be bassed on\nWHEN MATCHED THEN \n    UPDATE SET t.targetColumn = s.sourceColumn\nWHEN NOT MATCHED THEN\n    INSERT (targetColumn, [other columns ...]) VALUES (s.sourceColumn, [other values ..])\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 343535, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>MERGE is only available in SQL 2008 NOT SQL 2005</p>\n'}]
['https://Stackoverflow.com/questions/52356', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5433/']