How to insert column data from one table into another in SQL Server

Question

I'm trying to insert new_file_name column data from document_image_volume1_rename_temp table into the document_image_volume1_rename table. Both tables have a column document_image_id.

USE myDatabase

INSERT INTO document_image_volume1_rename (new_file_name)
   SELECT 
      new_file_name
   FROM  
      document_image_volume1_rename_temp
   WHERE 
      document_image_volume1_rename.document_image_id = document_image_volume1_rename_temp.document_image_id

Here's the error message:

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "document_image_volume1_rename.document_image_id" could not be bound.

So would I be correct to conclude that you don't actually want to **insert** new rows into `document_image_volume1_rename`?? That you actually want to **update** the value (from empty or value to the new value) in the **existing row**? — Mark Balhoff, Apr 23 '15 at 18:24

score 2 · Answer 1 · edited May 23 '17 at 10:24

I think what you are actually looking for is an UPDATE query not an INSERT query. If you don't want to add new rows but just modify a column in existing rows, try this:

UPDATE t2
SET t2.new_file_name = t1.new_file_name
FROM document_image_volume1_rename_temp t1 INNER JOIN document_image_volume1_rename t2 ON t1.document_image_id = t2.document_image_id

A handy reference on UPDATE query syntax by DBMS can be found in Eric's answer here.

How to insert column data from one table into another in SQL Server

1 Answers1