SQL INSERT INTO SELECT - existing rows

Question

I have looked around on the other questions but cant seem to find my answer.

When using the insert into select statement my query always creates new rows.

However I want a script that will insert data into a specific row where a condition is met.

For example: I have two tables, orders and Customers:

Orders:     
ORDER_Num     
CUSTOMER_ID  
CUSTOMER_NAME       
Order_Value

Customers: 
CUSTOMER_Num  
CUSTOMER_NAME    
Customer_Location

I want it so when I enter the Customer_NUM (This is the same as CUSTOMER_ID) I can run a script and it gets the Customer Name and populates the field with the corresponding Name.

I will probably create this as a stored procedure.

May I have the script in basic format ?

Resolved:

update ORDERS
set ORDERS.CUSTOMER_NAME = Customers.CUSTOMER_NAME
from orders
join Customers
on ORDERS.CUSTOMER_ID = Customers.CUSTOMER_Num

Answer 1

you must use UPDATE statement when you want to update some field of exisitng row. INSERT statement when you want to create a new row.

I try to explain better with some examples:

You want to add a new customer

INSERT INTO customer (field_name) VALUES (values_to_put_in_fields) 

We can suppose in your INSERT you don't know the document number of customer, but you know after some time.

So, you can write an UPDATE statement in this way

UPDATE customer 
   SET document_no = 'mydoc' 
 WHERE customer_id = 'id_of_your_customer'

If it's OK tell me, if I don't understand your answer tell me too ;)

Answer 2

If you are getting more advanced, there is the MERGE statement: http://msdn.microsoft.com/en-us/library/bb510625.aspx

If you are not a sql super-wiz then stick with the INSERT and UPDATE ... I usually do.