Inserts

Transact-SQL
1

I'm clearly missing something . I'm trying to insert a message from the customers table that's specific to that customer into the Invoice when its raised . I can add a process event in my system for that running of the query but , i keep getting an isnull error . I even went and set some default data into the customers table to get around the error but its still there . Clearly i'm being a bit thick . Any help appreciated

insert into salesinvoices (messages)
select ISNULL (DC_003_TXT ," " )  
from Customers
where customerid = 'lagabow_'

"Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Customer', table 'Training.dbo.SalesInvoices'; column does not allow nulls. INSERT fails.
The statement has been terminated."

2

If the salesinvoice table has a Customer column which is set to NOT NULL you would get the error.
If you only insert the message, how is the table going to know which customer it belongs to if you don't also insert that at the same time

3

HI , i was planning on using @Customer Parameter but at the testing stage i'm isolating one customer

insert into salesinvoices  (messages)
select ISNULL (DC_003_TXT ,'')  
from Customers
 where customer = '72631'
4

Is the problem resolved?

5

Nope , still cant get around it

6

you need to mention the customer column in your insert statement or else remove the not null constraint from the customer column.

7 
insert into salesinvoices (customer, messages)
select c.customerid, ISNULL (c.DC_003_TXT ," " )  
from Customers c
where c.customerid = 'lagabow_'
8

HI , My apologies , i actually needed an UPDATE not an INSERT , explains why i couldn't get it to work Many thanks for the assistants . Every days a school day

9

HI

I have the Update working fine but i want to be able to update the column leaving the originating text in place ,Is that possible

UPDATE salesinvoices
SET Salesinvoices.Messages =  customers.DC_003_TXT 
FROM customers INNER JOIN SalesInvoices ON salesinvoices.customer = Customers.Customer 
WHERE SalesInvoices.Customer = customers.Customer

something like this

UPDATE salesinvoices
SET Salesinvoices.Messages =  Concat ( customers.DC_003_TXT ,salesinvoices.message) 
FROM customers INNER JOIN SalesInvoices ON salesinvoices.customer = Customers.Customer 
WHERE SalesInvoices.Customer = customers.Customer