Emergency,Need for help with this insert then update, problem

[dpwsmw]

This insert works well, however, after I run the insert, every once in a while I want to run it again, and bring in any changes to the field, even if it is a new Customer. So the insert works well, but when I follow it up with an Update I get a failure.

Below is the insert, then the Update that fails and the error. Can anyone help with this?

INSERT dbo.tblArCust
(CustId,CustName,Addr1,Addr2,City,Region,PostalCode,SalesRepId1,TermsCode,PmtMethod,AcctType,DistCode,TaxLocId,Taxable,CurrencyId,TerrId)
SELECT CustomerCode, max (substring (CustomerName,1,25)), max (substring (Address1,1,25)), max (substring (Address2,1,25)), max (substring (City,1,15)), max (substring (State,1,2)), max (substring (Zip,1,5)), 'HSE', '30','Cash', '0','REG', 'NOTAX', '0', 'DOLLAR', max (substring (AddressType,1,10))
FROM COFFEE.dbo.vueCustomerAddress
GROUP BY CustomerCode, AddressTypeId
having count (*)>0 and addresstypeid = 1

update dbo.tblArCust
set custname = substring (CustomerName,1,25),
Addr1 = substring (Address1,1,25),
Addr2 = substring (Address2,1,25),
Region = substring (State,1,2),
PostalCode = substring (Zip,1,5),
SalesRepId1 ='HSE',
TermsCode = '30',
PmtMethod ='Cash',
AcctType ='0',
DistCode ='REG',
TaxLocId= 'NOTAX',
Taxable= '0',
CurrencyId= 'DOLLAR',
TerrId= substring (AddressType,1,10)
from dbo.tblArCust
join COFFEE.dbo.vueCustomerAddress
on dbo.tblArCust.CustId = COFFEE.dbo.vueCustomerAddress.CustomerId
and COFFEE.dbo.vueCustomerAddress.addresstypeid = 1 or COFFEE.dbo.vueCustomerAddress.addresstypeid

Error
Server: Msg 245, Level 16, State 1, Line 8
Syntax error converting the varchar value 'AARO007' to a column of data type int.

 

[PavanKumarU]

First what column is this data 'AARO007' coming from?
Second, have you tried including the insert statement in Begin and End transactions, so its commited? If you do that then the data is committed to the databsae and then you can try update it.
lemme know.

 

[dpwsmw]

I don't understand that question about it being committed, I am relatively new to SQL. and AARO007 refers to the CustId

 

[PavanKumarU]

put a BEGIN TRANSACTION before your insert statement and an END TRANSACTION after the insert statement.

 

[dpwsmw]

This did not work.

Begin Transaction
INSERT dbo.tblArCust
(CustId,CustName,Addr1,Addr2,City,Region,PostalCode,SalesRepId1,TermsCode,PmtMethod,AcctType,DistCode,TaxLocId,Taxable,CurrencyId,TerrId)

SELECT CustomerCode, max (substring (CustomerName,1,25)), max (substring (Address1,1,25)), max (substring (Address2,1,25)), max (substring (City,1,15)), max (substring (State,1,2)), max (substring (Zip,1,5)), 'HSE', '30','Cash', '0','REG', 'NOTAX', '0', 'DOLLAR', max (substring (AddressType,1,10))
FROM COFFEE.dbo.vueCustomerAddress
GROUP BY CustomerCode, AddressTypeId
having count (*)>0 and addresstypeid = 1
End Transaction

 

[PavanKumarU]

next thing to look at is if both custid and customerId are int columns or one is a varchar? can you send me the create sql for these two tables?

 

[miq]

hi,
make sure custid can except characters. It shouldn't be of int or other numeric datatypes.

B.R,
miq