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