Need help with sql statement. Cannot find

Greetings,
I have the following query:

truncate table propertyparameters;
	insert into PropertyParameters
	select
		SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID,
		0 as Score,
		0 as Score1,
		0 as Score2,
		0 as Score3,
		0 as Score4,
		0 as Score5,
		case
			when LEFT(PurchaseDate,4) = '0000' then 0
			else year(getdate()) - CAST(LEFT(PurchaseDate,4)as INT) 
			end as NumYearsOwned,
		0 as PrevNumYearsOwned,
		case
			when LEN(LoanDate2) > 2 then year(getdate()) - CAST(LEFT(LoanDate2,4)as INT)
			when LEN(LoanDate2) < 2 then 0
			else  year(getdate()) - CAST(LEFT(LoanDate1,4)as INT)
			end as NumYearsLastLoan,
		case when ISNUMERIC(AvailHomeEquity) = 1 then cast(AvailHomeEquity as float) else 0 end AS HomeEquity,
		case
			when ISNULL(HomeMarketValue,0) = 0 then 0
			when ISNUMERIC(HomeMarketValue) = 0 then 0
			when ISNUMERIC(TotalLoans) = 0 then 0
			when HomeMarketValue = 0 then 0
			else ISNULL(TotalLoans,0)/cast(HomeMarketValue as float)
			end as LoanToValue,
		case when ISNUMERIC(HomeMarketValue) = 1 then HomeMarketValue else 0 end AS MarketValueHome,
		case when ISNUMERIC(TotalLoans) = 1 then TotalLoans else 0 end AS TotalLoans,
		case
			when TrustOwned = 1 then 'T'
			else OwnerType
			end as OwnerType,
		PropertyTypeDetail as PropertyType,
		case
			when ISNUMERIC(EconStabInd) = 0 then 0
			else cast(EconStabInd as INT)
			end as OwnerEconStab,
		case
			when IncomeRange = 'A' then 10
			when IncomeRange = 'B' then 11
			when IncomeRange = 'C' then 12
			when IncomeRange = 'D' then 13
			when isnumeric(IncomeRange) = 1 then IncomeRange
			else 0
			end as OwnerIncome,
		case
			when ISNUMERIC(NetworthRange) = 1 then NetworthRange
			else 0
			end as OwnerNetworth,
		Gender as OwnerGender,
		case
			when MaritalStatus = 'M' then 'Y'
			when MaritalStatus = 'A' then 'Y'
			else 'N'
			end as OwnerMarriage,
		case
			when ChildF1617 = 1 then 'Y'
			when ChildM1617 = 1 then 'Y'
			when ChildU1617 = 1 then 'Y'
			else 'N'
			end as OwnerCollegeKids,
		SiteZip as ZipCode,
		case
			when MailOrderResponder = 'R' then 'Y'
			when MailOrderBuyer = 'B' then 'Y'
			else 'N'
			end as MailOrderResponder,
		Occupation as OwnerOccupation,
		1 as Updated,
		GETDATE() as DateStamp,
		PurchaseDate,
		0 as PurchasePrice,
		MONTH(GETDATE()) as Month,
		YEAR(GETDATE()) as Year,
		ltrim(rtrim(SiteHouseNumber)) as AddNum,
		ltrim(rtrim(SiteDirection)) as AddDir,
		ltrim(rtrim(SiteStreet)) as AddStreet,
		ltrim(rtrim(SiteUnitNum)) as AddUnitNum,
		ltrim(rtrim(SiteCity)) as AddCity,
		ltrim(rtrim(SiteState)) as AddState,
		ltrim(rtrim(SiteZip)) as AddZip,
		ltrim(rtrim(SiteZip4)) as AddZip4,
		ltrim(rtrim(SiteLat)) as AddLat,
		ltrim(rtrim(SiteLon)) as AddLon,
		case
			when ISNUMERIC(NumBeds) = 1 then CAST(NumBeds as INT)
			else 0
			end as NumBeds,
		0 as NumBath,
		case
			when PurchaseDate = '0' then NULL
			when right(PurchaseDate, 4) = '0000' then convert(date, left(PurchaseDate,4) + '1231', 112)
			when RIGHT(PurchaseDate, 2) = '00' then DATEADD(day,-1,DATEADD(month,cast(left(RIGHT(Purchasedate,4),2) AS INT),DATEADD(year,cast(LEFT(Purchasedate, 4) AS int)-1900,0)))
			else convert(date, ltrim(rtrim(cast(PurchaseDate as varchar(50)))), 112)
			end as SaleDate,
		0 as IsListed,
		0 as ID,
		NULL as GEOID,
		Phone,
		OwnerFName,
		OwnerMName,
		OwnerLName,
		case when ISNUMERIC(SiteLon) = 1 and ISNUMERIC(SiteLat) = 1 then
			geography::STPointFromText('POINT(' + SiteLon + ' ' + SiteLat + ')', 4326)
			else null
			end as GeoLocation,
		NULL as EmailChecked
	from dbo.PropertyNew

Open in new window


My problem is that I am receiving this error:
Conversion failed when converting the varchar value 'B' to data type int.  The problem is that the source table is 75M records strong. and I can't find the record that could cause the problem because the error is not telling me where to look, ie. what column.Can anyone look at the query and see if they see anything glaring wrong here?

Here's my Source Table:
CREATE TABLE [dbo].[PropertyNew](
	[Zip] [varchar](50) NULL,
	[Zip4] [varchar](50) NULL,
	[OwnerTitle] [varchar](50) NULL,
	[OwnerFName] [varchar](50) NULL,
	[OwnerMName] [varchar](50) NULL,
	[OwnerLName] [varchar](50) NULL,
	[MailStrNum10] [varchar](50) NULL,
	[MailStrDir] [varchar](50) NULL,
	[MailStr] [varchar](50) NULL,
	[MailCity] [varchar](50) NULL,
	[MailState] [varchar](50) NULL,
	[MailSecNum] [varchar](50) NULL,
	[AddressCat] [varchar](50) NULL,
	[NameCat] [varchar](50) NULL,
	[AddressLines] [varchar](50) NULL,
	[LineOfTravel] [varchar](50) NULL,
	[MailRecType] [varchar](50) NULL,
	[CRCCode] [varchar](50) NULL,
	[SeasonalMail] [varchar](50) NULL,
	[MailFIPSState] [varchar](50) NULL,
	[PurchaseDate] [varchar](50) NULL,
	[LengthResidence] [varchar](50) NULL,
	[LoanDate1] [varchar](50) NULL,
	[LoanDate2] [varchar](50) NULL,
	[SpouseFName] [varchar](50) NULL,
	[SpouseMName] [varchar](50) NULL,
	[SpouseLName] [varchar](50) NULL,
	[SpouseGender] [varchar](50) NULL,
	[SpouseTitle] [varchar](50) NULL,
	[SiteHouseNumber] [varchar](50) NULL,
	[SiteDirection] [varchar](50) NULL,
	[SiteStreet] [varchar](50) NULL,
	[SiteUnitNum] [varchar](50) NULL,
	[SiteCity] [varchar](50) NULL,
	[SiteState] [varchar](50) NULL,
	[SiteZip] [varchar](50) NULL,
	[SiteZip4] [varchar](50) NULL,
	[SiteLat] [varchar](50) NULL,
	[SiteLon] [varchar](50) NULL,
	[AvailHomeEquity] [varchar](50) NULL,
	[AvailHomeEquityRange] [varchar](50) NULL,
	[Fireplace] [varchar](50) NULL,
	[Garage] [varchar](50) NULL,
	[HeatCool] [varchar](50) NULL,
	[FirstLoanIntRateType] [varchar](50) NULL,
	[SecLoanIntRateType] [varchar](50) NULL,
	[LendableHomeEquity] [varchar](50) NULL,
	[LendableHomeEquityRange] [varchar](50) NULL,
	[FirstLoanLenderName] [varchar](50) NULL,
	[FirstLoanLenderType] [varchar](50) NULL,
	[LoanToValue] [varchar](50) NULL,
	[LoanToValueRange] [varchar](50) NULL,
	[FirstLoanAmount] [varchar](50) NULL,
	[SecondLoanAmount] [varchar](50) NULL,
	[FirstLoanAmountRange] [varchar](50) NULL,
	[SecLoanAmountRange] [varchar](50) NULL,
	[LotSize] [varchar](50) NULL,
	[LotSizeRange] [varchar](50) NULL,
	[HomeMarketValue] [varchar](50) NULL,
	[HomeMarketValueRange] [varchar](50) NULL,
	[NumBeds] [varchar](50) NULL,
	[NumberUnitsRange] [varchar](50) NULL,
	[TrustOwned] [varchar](50) NULL,
	[OwnerType] [varchar](50) NULL,
	[OwnerTypeDetail] [varchar](50) NULL,
	[Pool] [varchar](50) NULL,
	[PropertyType] [varchar](50) NULL,
	[PropertyTypeDetail] [varchar](50) NULL,
	[HomeSizeSQFT] [varchar](50) NULL,
	[HomeSizeSQFTRange] [varchar](50) NULL,
	[TotalLoans] [varchar](50) NULL,
	[Total_Loan_Range] [varchar](50) NULL,
	[HomePurchaseAmtRange] [varchar](50) NULL,
	[YearHomeBuilt] [varchar](50) NULL,
	[ChildM02] [varchar](50) NULL,
	[ChildF02] [varchar](50) NULL,
	[ChildU02] [varchar](50) NULL,
	[ChildM35] [varchar](50) NULL,
	[ChildF35] [varchar](50) NULL,
	[ChildU35] [varchar](50) NULL,
	[ChildM610] [varchar](50) NULL,
	[ChildF610] [varchar](50) NULL,
	[ChildU610] [varchar](50) NULL,
	[ChildM1115] [varchar](50) NULL,
	[ChildF1115] [varchar](50) NULL,
	[ChildU1115] [varchar](50) NULL,
	[ChildM1617] [varchar](50) NULL,
	[ChildF1617] [varchar](50) NULL,
	[ChildU1617] [varchar](50) NULL,
	[DwellingType] [varchar](50) NULL,
	[MaritalStatus] [varchar](50) NULL,
	[AssessedValue] [varchar](50) NULL,
	[AssessedValueRange] [varchar](50) NULL,
	[InvestorPropCounts] [varchar](50) NULL,
	[EconStabInd] [varchar](50) NULL,
	[PurchaseMonth] [varchar](50) NULL,
	[CBSA] [varchar](50) NULL,
	[Gender] [varchar](50) NULL,
	[NetworthRange] [varchar](50) NULL,
	[ParcelID] [varchar](50) NULL,
	[Phone] [varchar](50) NULL,
	[Occupation] [varchar](50) NULL,
	[MailOrderResponder] [varchar](50) NULL,
	[IncomeRange] [varchar](50) NULL,
	[MailOrderBuyer] [varchar](50) NULL,
	[InfobasePosMatch] [varchar](50) NULL,
	[NumSources] [varchar](50) NULL,
	[OverallMath] [varchar](50) NULL,
	[SuppressionMail] [varchar](50) NULL
) ON [PRIMARY]

Open in new window



and here's my target table:
CREATE TABLE [dbo].[PropertyParameters](

	[PropertyID] [varchar](50) NULL,

	[ParcelID] [varchar](50) NULL,

	[Score] [real] NULL,

	[Score1] [real] NULL,

	[Score2] [real] NULL,

	[NumYearsOwned] [int] NULL,

	[PrevNumYearsOwned] [int] NULL,

	[NumYearsLastLoan] [int] NULL,

	[HomeEquity] [real] NOT NULL,

	[LoanToValue] [real] NULL,

	[MarketValueHome] [real] NULL,

	[TotalLoans] [real] NULL,

	[OwnerType] [varchar](50) NULL,

	[PropertyType] [varchar](50) NOT NULL,

	[OwnerEconStab] [int] NULL,

	[OwnerIncome] [int] NULL,

	[OwnerNetworth] [int] NULL,

	[OwnerGender] [varchar](50) NULL,

	[OwnerMarriage] [varchar](50) NULL,

	[OwnerCollegeKids] [varchar](50) NULL,

	[ZipCode] [varchar](50) NULL,

	[MailOrderResponder] [varchar](50) NULL,

	[OwnerOccupation] [varchar](50) NULL,

	[Updated] [int] NULL,

	[DateStamp] [smalldatetime] NULL,

	[PurchaseDate] [varchar](50) NULL,

	[PurchasePrice] [real] NULL,

	[Month] [varchar](50) NULL,

	[Year] [varchar](50) NULL,

	[AddNum] [varchar](50) NULL,

	[AddDir] [varchar](50) NULL,

	[AddStreet] [varchar](50) NULL,

	[AddUnitNum] [varchar](50) NULL,

	[AddCity] [varchar](50) NULL,

	[AddState] [varchar](50) NULL,

	[AddZip] [varchar](50) NULL,

	[AddZip4] [varchar](50) NULL,

	[AddLat] [varchar](50) NULL,

	[AddLon] [varchar](50) NULL,

	[NumBeds] [int] NULL,

	[NumBath] [int] NULL,

	[SaleDate] [date] NULL,

	[IsListed] [int] NULL,

	[ID] [int] NULL,

	[GEOID] [varchar](50) NULL,

	[Phone] [varchar](50) NULL,

	[OwnerFName] [varchar](50) NULL,

	[OwnerMName] [varchar](50) NULL,

	[OwnerLName] [varchar](50) NULL,

	[GeoLocation] [geography] NULL,

	[EmailChecked] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Use an explicit field list in INSERT. Always. Naming the columns in SELECT does not ensure exactly those columns are set in INSERT - there is only a positional reference, not by name.
I see you selecting 6 scores, but there are only 3 in the target table.
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks.
There's six scores in the target table.  I just used a bad  target code in my question.

I updated the insert statement as follows.  any other ideas?

	insert into PropertyParameters
	([PropertyID],[ParcelID],[Score],[Score1],[Score2]
     ,[Score3],[Score4],[Score5],[NumYearsOwned],[PrevNumYearsOwned]
     ,[NumYearsLastLoan],[HomeEquity],[LoanToValue],[MarketValueHome],[TotalLoans]
     ,[OwnerType],[PropertyType],[OwnerEconStab],[OwnerIncome],[OwnerNetworth]
     ,[OwnerGender],[OwnerMarriage],[OwnerCollegeKids],[ZipCode],[MailOrderResponder]
     ,[OwnerOccupation],[Updated],[DateStamp],[PurchaseDate],[PurchasePrice]
     ,[Month],[Year],[AddNum],[AddDir],[AddStreet]
     ,[AddUnitNum],[AddCity],[AddState],[AddZip],[AddZip4]
     ,[AddLat],[AddLon],[NumBeds],[NumBath],[SaleDate]
     ,[IsListed],[ID],[GEOID],[Phone],[OwnerFName]
     ,[OwnerMName],[OwnerLName],[GeoLocation],[EmailChecked])
	select
		SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID,
		0 as Score,
		0 as Score1,
		0 as Score2,
		0 as Score3,
		0 as Score4,
		0 as Score5,
		case
			when LEFT(PurchaseDate,4) = '0000' then 0
			else year(getdate()) - CAST(LEFT(PurchaseDate,4)as INT) 
			end as NumYearsOwned,
		0 as PrevNumYearsOwned,
		case
			when LEN(LoanDate2) > 2 then year(getdate()) - CAST(LEFT(LoanDate2,4)as INT)
			when LEN(LoanDate2) < 2 then 0
			else  year(getdate()) - CAST(LEFT(LoanDate1,4)as INT)
			end as NumYearsLastLoan,
		case when ISNUMERIC(AvailHomeEquity) = 1 then cast(AvailHomeEquity as float) else 0 end AS HomeEquity,
		case
			when ISNULL(HomeMarketValue,0) = 0 then 0
			when ISNUMERIC(HomeMarketValue) = 0 then 0
			when ISNUMERIC(TotalLoans) = 0 then 0
			when HomeMarketValue = 0 then 0
			else ISNULL(TotalLoans,0)/cast(HomeMarketValue as float)
			end as LoanToValue,
		case when ISNUMERIC(HomeMarketValue) = 1 then HomeMarketValue else 0 end AS MarketValueHome,
		case when ISNUMERIC(TotalLoans) = 1 then TotalLoans else 0 end AS TotalLoans,
		case
			when TrustOwned = 1 then 'T'
			else OwnerType
			end as OwnerType,
		PropertyTypeDetail as PropertyType,
		case
			when ISNUMERIC(EconStabInd) = 0 then 0
			else cast(EconStabInd as INT)
			end as OwnerEconStab,
		case
			when IncomeRange = 'A' then 10
			when IncomeRange = 'B' then 11
			when IncomeRange = 'C' then 12
			when IncomeRange = 'D' then 13
			when isnumeric(IncomeRange) = 1 then IncomeRange
			else 0
			end as OwnerIncome,
		case
			when ISNUMERIC(NetworthRange) = 1 then NetworthRange
			else 0
			end as OwnerNetworth,
		Gender as OwnerGender,
		case
			when MaritalStatus = 'M' then 'Y'
			when MaritalStatus = 'A' then 'Y'
			else 'N'
			end as OwnerMarriage,
		case
			when ChildF1617 = 1 then 'Y'
			when ChildM1617 = 1 then 'Y'
			when ChildU1617 = 1 then 'Y'
			else 'N'
			end as OwnerCollegeKids,
		SiteZip as ZipCode,
		case
			when MailOrderResponder = 'R' then 'Y'
			when MailOrderBuyer = 'B' then 'Y'
			else 'N'
			end as MailOrderResponder,
		Occupation as OwnerOccupation,
		1 as Updated,
		GETDATE() as DateStamp,
		PurchaseDate,
		0 as PurchasePrice,
		MONTH(GETDATE()) as Month,
		YEAR(GETDATE()) as Year,
		ltrim(rtrim(SiteHouseNumber)) as AddNum,
		ltrim(rtrim(SiteDirection)) as AddDir,
		ltrim(rtrim(SiteStreet)) as AddStreet,
		ltrim(rtrim(SiteUnitNum)) as AddUnitNum,
		ltrim(rtrim(SiteCity)) as AddCity,
		ltrim(rtrim(SiteState)) as AddState,
		ltrim(rtrim(SiteZip)) as AddZip,
		ltrim(rtrim(SiteZip4)) as AddZip4,
		ltrim(rtrim(SiteLat)) as AddLat,
		ltrim(rtrim(SiteLon)) as AddLon,
		case
			when ISNUMERIC(NumBeds) = 1 then CAST(NumBeds as INT)
			else 0
			end as NumBeds,
		0 as NumBath,
		case
			when PurchaseDate = '0' then NULL
			when right(PurchaseDate, 4) = '0000' then convert(date, left(PurchaseDate,4) + '1231', 112)
			when RIGHT(PurchaseDate, 2) = '00' then DATEADD(day,-1,DATEADD(month,cast(left(RIGHT(Purchasedate,4),2) AS INT),DATEADD(year,cast(LEFT(Purchasedate, 4) AS int)-1900,0)))
			else convert(date, ltrim(rtrim(cast(PurchaseDate as varchar(50)))), 112)
			end as SaleDate,
		0 as IsListed,
		0 as ID,
		NULL as GEOID,
		Phone,
		OwnerFName,
		OwnerMName,
		OwnerLName,
		case when ISNUMERIC(SiteLon) = 1 and ISNUMERIC(SiteLat) = 1 then
			geography::STPointFromText('POINT(' + SiteLon + ' ' + SiteLat + ')', 4326)
			else null
			end as GeoLocation,
		NULL as EmailChecked
	from dbo.PropertyNew

Open in new window

0
Ryan ChongCommented:
>>Conversion failed when converting the varchar value 'B' to data type int.

try debugging by inserting less column's value and see which column got the conversion error.

like try:
insert into PropertyParameters ([PropertyID],[ParcelID],[Score],[Score1],[Score2] ) select SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID, 0 as Score,0 as Score1,0 as Score2 from ...

Open in new window

and then:
insert into PropertyParameters ([PropertyID],[ParcelID],[Score],[Score1],[Score2] ,[Score3],[Score4],[Score5] ) select SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID, 0 as Score,0 as Score1,0 as Score2,	0 as Score3,0 as Score4,0 as Score5 from ...

Open in new window

and so on...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If having to try with less fields  - I have done that myself on occasion -  split the fields in halfs, but always include the primary key of course. One of the two generated queries will fail. Use the failing one, again split it into two, and repeat. This is the fastest way needing least tests.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should explicit the insert columns:
insert into PropertyParameters (ColName1, ColName2, ..., ColNameN)
select 
(...)

Open in new window

0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thanks Guys.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I disagree to Vitor getting points. The very first comment was "Use an explicit field list in INSERT. Always.", and it was me posting. Vitor just repeated that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.