August 17, 2006 at 4:55 pm
I know there is a better way to do this, but am in a bind and have to get this up now.
We have county tables for everycounty in a state.
I need to insert into a 'county'table with a select and have it run through all the counties.
I'm getting this error
Msg 402, Level 16, State 1, Line 15
The data types varchar and text are incompatible in the add operator.
--Here's the proc and the schema of both tables is below.
declare
@countyname varchar (255)
declare
@sql varchar(8000)
declare
county_name cursor for
select
replace(county, ' ','') from ZipTrendIndex
group
by county
open
county_name
fetch
next from county_name
into
@countyname
WHILE
@@FETCH_STATUS = 0
BEGIN
set @sql = 'INSERT INTO [county'+@countyname+']([SA_PROPERTY_ID,
,[mm_FIPS_STATE_CODE]
,[mm_FIPS_MUNI_CODE]
,[MM_MUNI_CODE]
,[SA_PARCEL_NBR_PRIMARY]
,[SA_OWNER_1]
,[SA_OWNER_1_ET_FLAG]
,[SA_OWNER_1_FIRST]
,[SA_OWNER_1_GROUP]
,[SA_OWNER_1_LAST]
,[SA_OWNER_1_MID]
,[SA_OWNER_1_PRE]
,[SA_OWNER_1_SP_FIRST]
,[SA_OWNER_1_SP_MID]
,[SA_OWNER_1_SP_SUF]
,[SA_OWNER_1_SUF]
,[SA_OWNER_2]
,[SA_OWNER_2_FIRST]
,[SA_OWNER_2_LAST]
,[SA_OWNER_2_MID]
,[SA_OWNER_2_SP_FIRST]
,[SA_OWNER_2_SP_MID]
,[SA_OWNER_2_SUF]
,[SA_OWNERSHIP_STATUS_CODE]
,[SA_SITE_HOUSE_NBR]
,[SA_SITE_FRACTION]
,[SA_SITE_DIR]
,[SA_SITE_STREET_NAME]
,[SA_SITE_SUF]
,[SA_SITE_POST_DIR]
,[SA_SITE_UNIT_PRE]
,[SA_SITE_UNIT_VAL]
,[SA_SITE_CITY_STATE]
,[SA_SITE_ZIP]
,[SA_SITE_City]
,[SA_SITE_State]
,[SA_SITE_CRRT]
,[SA_SITE_PLUS_4]
,[SR_SITE_ADDR_RAW]
,[SA_MAIL_HOUSE_NBR]
,[SA_MAIL_FRACTION]
,[SA_MAIL_DIR]
,[SA_MAIL_STREET_NAME]
,[SA_MAIL_SUF]
,[SA_MAIL_POST_DIR]
,[SA_MAIL_UNIT_PRE]
,[SA_MAIL_UNIT_VAL]
,[SA_MAIL_CITY_STATE]
,[SA_MAIL_STATE]
,[SA_MAIL_ZIP]
,[SA_SITE_MAIL_SAME]
,[SA_LGL_DSCRPTN]
,[SA_TOWNSHIP]
,[SA_PHONE_NBR]
,[SA_PRIVACY_CODE]
,[USE_CODE_MUNI]
,[USE_CODE_STD]
,[SA_ZONING]
,[MM_ASSESSMENT_YEAR]
,[SA_VAL_ASSD]
,[SA_VAL_ASSD_IMPRV]
,[SA_VAL_ASSD_LAND]
,[SA_IMPRV_PCT]
,[SA_TAX_VAL]
,[SA_TAX_YEAR_DELINQ]
,[SA_VAL_FULL_CASH]
,[SA_VAL_CURRENT_LIMIT]
,[SA_VAL_MARKET]
,[SA_APPRAISE_VAL]
,[SA_YR_BLT]
,[SA_YR_BLT_EFFECT]
,[SA_BLDG_SHAPE_CODE]
,[SA_ARCHITECTURE_CODE]
,[SA_STRUCTURE_CODE]
,[SA_EXTERIOR_1_CODE]
,[SA_EXTERIOR_2_CODE]
,[SA_CONSTRUCTION_CODE]
,[SA_CONSTRUCTION_QLTY]
,[SA_LOT_DEPTH]
,[SA_LOT_WIDTH]
,[SA_LOTSIZE]
,[SA_SQFT]
,[SA_SQFT_DQ]
,[SA_ADDTNS_SQFT]
,[SA_ATTIC_SQFT]
,[SA_BSMT_FIN_SQFT]
,[SA_GRG_SQFT_1]
,[SA_HEATING_COOLING]
,[SA_HEATING_DETAIL]
,[SA_COOLING_DETAIL]
,[SA_FIREPLACE_CODE]
,[SA_GARAGE_CARPORT]
,[SA_NBR_BATH]
,[SA_NBR_BEDRMS]
,[SA_NBR_RMS]
,[SA_NBR_STORIES]
,[SA_NBR_UNITS]
,[SA_POOL_CODE]
,[SA_ROOF_CODE]
,[SA_VIEW_CODE]
,[SA_DATE_TRANSFER]
,[SA_VAL_TRANSFER]
,[SR_BUYER]
,[SR_SELLER]
,[SR_ARMS_LENGTH_FLAG]
,[SR_TRAN_TYPE]
,[SR_FULL_PART_CODE]
,[SR_MULT_APN_FLAG_KEYED]
,[SR_MULT_PORT_CODE]
,[SA_DATE_NOVAL_TRANSFER]
,[AsARMS_LENGTH_FLAG_DFS]
,[SA_LOAN_VAL_1]
,[SR_LOAN_VAL_2]
,[SR_LOAN_VAL_3]
,[HEDONIC_VALUE]
,[ASSESSED_VALUE]
,[RSI_VALUE]
,[APPR_EMUL_VALUE]
,[AVM_VALUE]
,[ReplyFRSI]
,[ReplyAVMSQFT]
,[ReplyAVMLotSize]
,[ReplyAVM]
,[ReplyScore]
,[HEDONICWeight]
,[ASSESSEDWeight]
,[RSIWeight]
,[APPR_EMULWeight]
,[AVMFRSIWeight]
,[AVMSqurWeight]
,[AVMLotWeight]
,[Alpha_Reliability_Score]
,[RELIABILITY_SCORE]
,[SA_X_COORD]
,[SA_Y_COORD]
,[Longitude]
,[Latitude]
,[SA_CENSUS_TRACT]
,[SA_CENSUS_BLOCK_GROUP]
,[SA_GEO_QLTY_CODE]
,[FILLER]
,[Street_name_soundex])
select SA_PROPERTY_ID,
mm_FIPS_STATE_CODE,
mm_FIPS_MUNI_CODE,
MM_MUNI_CODE,
SA_PARCEL_NBR_PRIMARY,
SA_OWNER_1,
SA_OWNER_1_ET_FLAG,
SA_OWNER_1_FIRST,
SA_OWNER_1_GROUP,
SA_OWNER_1_LAST,
SA_OWNER_1_MID,
SA_OWNER_1_PRE,
SA_OWNER_1_SP_FIRST,
SA_OWNER_1_SP_MID,
SA_OWNER_1_SP_SUF,
SA_OWNER_1_SUF,
SA_OWNER_2,
SA_OWNER_2_FIRST,
SA_OWNER_2_LAST,
SA_OWNER_2_MID,
SA_OWNER_2_SP_FIRST,
SA_OWNER_2_SP_MID,
SA_OWNER_2_SUF,
SA_OWNERSHIP_STATUS_CODE,
SA_SITE_HOUSE_NBR,
SA_SITE_FRACTION,
SA_SITE_DIR,
SA_SITE_STREET_NAME,
SA_SITE_SUF,
SA_SITE_POST_DIR,
SA_SITE_UNIT_PRE,
SA_SITE_UNIT_VAL,
SA_SITE_CITY_STATE,
SA_SITE_ZIP,
SA_SITE_City,
SA_SITE_State,
SA_SITE_CRRT,
SA_SITE_PLUS_4,
SR_SITE_ADDR_RAW,
SA_MAIL_HOUSE_NBR,
SA_MAIL_FRACTION,
SA_MAIL_DIR,
SA_MAIL_STREET_NAME,
SA_MAIL_SUF,
SA_MAIL_POST_DIR,
SA_MAIL_UNIT_PRE,
SA_MAIL_UNIT_VAL,
SA_MAIL_CITY_STATE,
SA_MAIL_STATE,
SA_MAIL_ZIP,
SA_SITE_MAIL_SAME,
SA_LGL_DSCRPTN,
SA_TOWNSHIP,
SA_PHONE_NBR,
SA_PRIVACY_CODE,
USE_CODE_MUNI,
USE_CODE_STD,
SA_ZONING,
MM_ASSESSMENT_YEAR,
SA_VAL_ASSD,
SA_VAL_ASSD_IMPRV,
SA_VAL_ASSD_LAND,
SA_IMPRV_PCT,
SA_TAX_VAL,
SA_TAX_YEAR_DELINQ,
SA_VAL_FULL_CASH,
SA_VAL_CURRENT_LIMIT,
SA_VAL_MARKET,
SA_APPRAISE_VAL,
SA_YR_BLT,
SA_YR_BLT_EFFECT,
SA_BLDG_SHAPE_CODE,
SA_ARCHITECTURE_CODE,
SA_STRUCTURE_CODE,
SA_EXTERIOR_1_CODE,
SA_EXTERIOR_2_CODE,
SA_CONSTRUCTION_CODE,
SA_CONSTRUCTION_QLTY,
SA_LOT_DEPTH,
SA_LOT_WIDTH,
SA_LOTSIZE,
SA_SQFT,
SA_SQFT_DQ,
SA_ADDTNS_SQFT,
SA_ATTIC_SQFT,
SA_BSMT_FIN_SQFT,
SA_GRG_SQFT_1,
SA_HEATING_COOLING,
SA_HEATING_DETAIL,
SA_COOLING_DETAIL,
SA_FIREPLACE_CODE,
SA_GARAGE_CARPORT,
SA_NBR_BATH,
SA_NBR_BEDRMS,
SA_NBR_RMS,
SA_NBR_STORIES,
SA_NBR_UNITS,
SA_POOL_CODE,
SA_ROOF_CODE,
SA_VIEW_CODE,
SA_DATE_TRANSFER,
SA_VAL_TRANSFER,
SR_BUYER,
SR_SELLER,
SR_ARMS_LENGTH_FLAG,
SR_TRAN_TYPE,
SR_FULL_PART_CODE,
SR_MULT_APN_FLAG_KEYED,
SR_MULT_PORT_CODE,
SA_DATE_NOVAL_TRANSFER,
AsARMS_LENGTH_FLAG_DFS,
SA_LOAN_VAL_1,
SR_LOAN_VAL_2,
SR_LOAN_VAL_3,
HEDONIC_VALUE,
ASSESSED_VALUE,
RSI_VALUE,
APPR_EMUL_VALUE,
AVM_VALUE,
ReplyFRSI,
ReplyAVMSQFT,
ReplyAVMLotSize,
ReplyAVM,
ReplyScore,
HEDONICWeight,
ASSESSEDWeight,
RSIWeight,
APPR_EMULWeight,
AVMFRSIWeight,
AVMSqurWeight,
AVMLotWeight,
Alpha_Reliability_Score,
RELIABILITY_SCORE,
SA_X_COORD,
SA_Y_COORD,
p.Longitude,
p.Latitude,
SA_CENSUS_TRACT,
SA_CENSUS_BLOCK_GROUP,
SA_GEO_QLTY_CODE,
FILLER,
Street_name_soundex
from propertydetails p join propertycenter..zipcodes z on p.SA_MAIL_ZIP = z.zipcode
where z.county = '
+@countyname+'
'
exec (@sql)
end
fetch
next from county_name into @countyname
CLOSE
county_name
DEALLOCATE
county_name
USE
[PropertyCA]
GO
/****** Object: Table [dbo].[PropertyDetails] Script Date: 08/17/2006 15:48:55 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[PropertyDetails](
[SA_PROPERTY_ID] [int]
NOT NULL,
[mm_FIPS_STATE_CODE] [tinyint]
NOT NULL,
[mm_FIPS_MUNI_CODE] [smallint]
NOT NULL,
[MM_MUNI_CODE] [char]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SA_PARCEL_NBR_PRIMARY] [varchar]
(35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_ET_FLAG] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_FIRST] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_GROUP] [varchar]
(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_LAST] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_MID] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_PRE] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_SP_FIRST] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_SP_MID] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_SP_SUF] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_1_SUF] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_FIRST] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_LAST] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_MID] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_SP_FIRST] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_SP_MID] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNER_2_SUF] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_OWNERSHIP_STATUS_CODE] [char]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_HOUSE_NBR] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_FRACTION] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_DIR] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_STREET_NAME] [varchar]
(40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_SUF] [varchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_POST_DIR] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_UNIT_PRE] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_UNIT_VAL] [varchar]
(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_CITY_STATE] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_ZIP] [int]
NULL,
[SA_SITE_City] [varchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_State] [char]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_CRRT] [varchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_SITE_PLUS_4] [smallint]
NULL,
[SR_SITE_ADDR_RAW] [varchar]
(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_HOUSE_NBR] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_FRACTION] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_DIR] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_STREET_NAME] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_SUF] [varchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_POST_DIR] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_UNIT_PRE] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_UNIT_VAL] [varchar]
(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_CITY_STATE] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_STATE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_MAIL_ZIP] [int]
NULL,
[SA_SITE_MAIL_SAME] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_LGL_DSCRPTN] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_TOWNSHIP] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_PHONE_NBR] [varchar]
(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_PRIVACY_CODE] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USE_CODE_MUNI] [varchar]
(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USE_CODE_STD] [varchar]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_ZONING] [varchar]
(13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MM_ASSESSMENT_YEAR] [smallint]
NULL,
[SA_VAL_ASSD] [int]
NULL,
[SA_VAL_ASSD_IMPRV] [int]
NULL,
[SA_VAL_ASSD_LAND] [int]
NULL,
[SA_IMPRV_PCT] [int]
NULL,
[SA_TAX_VAL] [numeric]
(9, 2) NULL,
[SA_TAX_YEAR_DELINQ] [smallint]
NULL,
[SA_VAL_FULL_CASH] [int]
NULL,
[SA_VAL_CURRENT_LIMIT] [int]
NULL,
[SA_VAL_MARKET] [int]
NULL,
[SA_APPRAISE_VAL] [int]
NULL,
[SA_YR_BLT] [smallint]
NULL,
[SA_YR_BLT_EFFECT] [smallint]
NULL,
[SA_BLDG_SHAPE_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_ARCHITECTURE_CODE] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_STRUCTURE_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_EXTERIOR_1_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_EXTERIOR_2_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_CONSTRUCTION_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_CONSTRUCTION_QLTY] [numeric]
(3, 1) NULL,
[SA_LOT_DEPTH] [smallint]
NULL,
[SA_LOT_WIDTH] [smallint]
NULL,
[SA_LOTSIZE] [numeric]
(14, 4) NULL,
[SA_SQFT] [int]
NULL,
[SA_SQFT_DQ] [int]
NULL,
[SA_ADDTNS_SQFT] [smallint]
NULL,
[SA_ATTIC_SQFT] [smallint]
NULL,
[SA_BSMT_FIN_SQFT] [smallint]
NULL,
[SA_GRG_SQFT_1] [smallint]
NULL,
[SA_HEATING_COOLING] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_HEATING_DETAIL] [tinyint]
NULL,
[SA_COOLING_DETAIL] [tinyint]
NULL,
[SA_FIREPLACE_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_GARAGE_CARPORT] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_NBR_BATH] [numeric]
(5, 2) NULL,
[SA_NBR_BEDRMS] [smallint]
NULL,
[SA_NBR_RMS] [smallint]
NULL,
[SA_NBR_STORIES] [tinyint]
NULL,
[SA_NBR_UNITS] [smallint]
NULL,
[SA_POOL_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_ROOF_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_VIEW_CODE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_DATE_TRANSFER] [int]
NULL,
[SA_VAL_TRANSFER] [int]
NULL,
[SR_BUYER] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_SELLER] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_ARMS_LENGTH_FLAG] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_TRAN_TYPE] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_FULL_PART_CODE] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_MULT_APN_FLAG_KEYED] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SR_MULT_PORT_CODE] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_DATE_NOVAL_TRANSFER] [int]
NULL,
[AsARMS_LENGTH_FLAG_DFS] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_LOAN_VAL_1] [int]
NULL,
[SR_LOAN_VAL_2] [int]
NULL,
[SR_LOAN_VAL_3] [int]
NULL,
[HEDONIC_VALUE] [int]
NULL,
[ASSESSED_VALUE] [int]
NULL,
[RSI_VALUE] [int]
NULL,
[APPR_EMUL_VALUE] [int]
NULL,
[AVM_VALUE] [int]
NULL,
[ReplyFRSI] [int]
NULL,
[ReplyAVMSQFT] [int]
NULL,
[ReplyAVMLotSize] [int]
NULL,
[ReplyAVM] [int]
NULL,
[ReplyScore] [int]
NULL,
[HEDONICWeight] [int]
NULL,
[ASSESSEDWeight] [int]
NULL,
[RSIWeight] [int]
NULL,
[APPR_EMULWeight] [int]
NULL,
[AVMFRSIWeight] [int]
NULL,
[AVMSqurWeight] [int]
NULL,
[AVMLotWeight] [int]
NULL,
[Alpha_Reliability_Score] [char]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RELIABILITY_SCORE] [int]
NULL,
[SA_X_COORD] [float]
NULL,
[SA_Y_COORD] [float]
NULL,
[Longitude] [float]
NULL,
[Latitude] [float]
NULL,
[SA_CENSUS_TRACT] [varchar]
(7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_CENSUS_BLOCK_GROUP] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SA_GEO_QLTY_CODE] [varchar]
(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FILLER] [varchar]
(13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Replypct] [float]
NULL,
[ReplyAvg] [float]
NULL,
[ReplyPctWeight] [int]
NULL,
[ReplyAvgWeight] [int]
NULL,
[STREET_NAME_SOUNDEX] [char]
(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
/****** Object: Table [dbo].[ZipCodes] Script Date: 08/17/2006 15:49:24 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ZipCodes](
[ZipCode] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar]
(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [varchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Latitude] [float]
NULL,
[Longitude] [float]
NULL,
[areacode] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [varchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
August 17, 2006 at 5:20 pm
I observed in your procedure.. In where condition you are comparing two datatypes without explicit conversion
SA_MAIL_ZIP is of int and
zipcode is of
Varchar(5)
use convert() function when comparing different data types
August 17, 2006 at 5:47 pm
I tried converting the varchar to an int or the int to a varchar, but I still get the same message. Should I convert in the select or where clause?
August 17, 2006 at 5:53 pm
Modify the following where clause
p.SA_MAIL_ZIP = z.zipcode
to
convert(varchar(5),p.SA_MAIL_ZIP) = z.zipcode
August 17, 2006 at 6:42 pm
Yes, I tried that.
It actually turned out to be that the string was too long, so I broke the insert into two, but can't get it to loop through the counties, what am I missing? Please help been working for the last 36 hours straight and my brain hurts..
Thanks in advance
Susan
declare
@countyname varchar (200)
declare
@sql varchar(8000)
declare
@sql2 varchar(8000)
declare
county_name cursor for
select distinct county from ZipTrendIndex
open
county_name
fetch
next from county_name
into
@countyname
WHILE
@@FETCH_STATUS = 0
BEGIN
set @sql = 'INSERT INTO PropertyCA.dbo.[county'+@countyname+']
(SA_PROPERTY_ID
,mm_FIPS_STATE_CODE
,mm_FIPS_MUNI_CODE
,MM_MUNI_CODE
,SA_PARCEL_NBR_PRIMARY
,SA_OWNER_1
,SA_OWNER_1_ET_FLAG
,SA_OWNER_1_FIRST
,SA_OWNER_1_GROUP
,SA_OWNER_1_LAST
,SA_OWNER_1_MID
,SA_OWNER_1_PRE
,SA_OWNER_1_SP_FIRST
,SA_OWNER_1_SP_MID
,SA_OWNER_1_SP_SUF
,SA_OWNER_1_SUF
,SA_OWNER_2
,SA_OWNER_2_FIRST
,SA_OWNER_2_LAST
,SA_OWNER_2_MID
,SA_OWNER_2_SP_FIRST
,SA_OWNER_2_SP_MID
,SA_OWNER_2_SUF
,SA_OWNERSHIP_STATUS_CODE
,SA_SITE_HOUSE_NBR
,SA_SITE_FRACTION
,SA_SITE_DIR
,SA_SITE_STREET_NAME
,SA_SITE_SUF
,SA_SITE_POST_DIR
,SA_SITE_UNIT_PRE
,SA_SITE_UNIT_VAL
,SA_SITE_CITY_STATE
,SA_SITE_ZIP
,SA_SITE_City
,SA_SITE_State
,SA_SITE_CRRT
,SA_SITE_PLUS_4
,SR_SITE_ADDR_RAW
,SA_MAIL_HOUSE_NBR
,SA_MAIL_FRACTION
,SA_MAIL_DIR
,SA_MAIL_STREET_NAME
,SA_MAIL_SUF
,SA_MAIL_POST_DIR
,SA_MAIL_UNIT_PRE
,SA_MAIL_UNIT_VAL
,SA_MAIL_CITY_STATE
,SA_MAIL_STATE
,SA_MAIL_ZIP
,SA_SITE_MAIL_SAME
,SA_LGL_DSCRPTN
,SA_TOWNSHIP
,SA_PHONE_NBR
,SA_PRIVACY_CODE
,USE_CODE_MUNI
,USE_CODE_STD
,SA_ZONING
,MM_ASSESSMENT_YEAR
,SA_VAL_ASSD
,SA_VAL_ASSD_IMPRV
,SA_VAL_ASSD_LAND
,SA_IMPRV_PCT
,SA_TAX_VAL
,SA_TAX_YEAR_DELINQ
,SA_VAL_FULL_CASH
,SA_VAL_CURRENT_LIMIT
,SA_VAL_MARKET
,SA_APPRAISE_VAL
,SA_YR_BLT
,SA_YR_BLT_EFFECT
,SA_BLDG_SHAPE_CODE
,SA_ARCHITECTURE_CODE
,SA_STRUCTURE_CODE
,SA_EXTERIOR_1_CODE
,SA_EXTERIOR_2_CODE
,SA_CONSTRUCTION_CODE
,SA_CONSTRUCTION_QLTY
,SA_LOT_DEPTH
,SA_LOT_WIDTH
,SA_LOTSIZE
,SA_SQFT
,SA_SQFT_DQ
,SA_ADDTNS_SQFT
,SA_ATTIC_SQFT
,SA_BSMT_FIN_SQFT
,SA_GRG_SQFT_1
,SA_HEATING_COOLING
,SA_HEATING_DETAIL
,SA_COOLING_DETAIL
,SA_FIREPLACE_CODE
,SA_GARAGE_CARPORT
,SA_NBR_BATH
,SA_NBR_BEDRMS
,SA_NBR_RMS
,SA_NBR_STORIES
,SA_NBR_UNITS
,SA_POOL_CODE
,SA_ROOF_CODE
,SA_VIEW_CODE
,SA_DATE_TRANSFER
,SA_VAL_TRANSFER
,SR_BUYER
,SR_SELLER
,SR_ARMS_LENGTH_FLAG
,SR_TRAN_TYPE
,SR_FULL_PART_CODE
,SR_MULT_APN_FLAG_KEYED
,SR_MULT_PORT_CODE
,SA_DATE_NOVAL_TRANSFER
,AsARMS_LENGTH_FLAG_DFS
,SA_LOAN_VAL_1
,SR_LOAN_VAL_2
,SR_LOAN_VAL_3
,HEDONIC_VALUE
,ASSESSED_VALUE
,RSI_VALUE
,APPR_EMUL_VALUE
,AVM_VALUE
,ReplyAVM
,ReplyScore
,Alpha_Reliability_Score
,RELIABILITY_SCORE
,SA_X_COORD
,SA_Y_COORD
,Longitude
,Latitude
,SA_CENSUS_TRACT
,SA_CENSUS_BLOCK_GROUP
,SA_GEO_QLTY_CODE
,Street_name_soundex)'
set @sql2 = 'SELECT SA_PROPERTY_ID
,mm_FIPS_STATE_CODE
,mm_FIPS_MUNI_CODE
,MM_MUNI_CODE
,SA_PARCEL_NBR_PRIMARY
,SA_OWNER_1
,SA_OWNER_1_ET_FLAG
,SA_OWNER_1_FIRST
,SA_OWNER_1_GROUP
,SA_OWNER_1_LAST
,SA_OWNER_1_MID
,SA_OWNER_1_PRE
,SA_OWNER_1_SP_FIRST
,SA_OWNER_1_SP_MID
,SA_OWNER_1_SP_SUF
,SA_OWNER_1_SUF
,SA_OWNER_2
,SA_OWNER_2_FIRST
,SA_OWNER_2_LAST
,SA_OWNER_2_MID
,SA_OWNER_2_SP_FIRST
,SA_OWNER_2_SP_MID
,SA_OWNER_2_SUF
,SA_OWNERSHIP_STATUS_CODE
,SA_SITE_HOUSE_NBR
,SA_SITE_FRACTION
,SA_SITE_DIR
,SA_SITE_STREET_NAME
,SA_SITE_SUF
,SA_SITE_POST_DIR
,SA_SITE_UNIT_PRE
,SA_SITE_UNIT_VAL
,SA_SITE_CITY_STATE
,SA_SITE_ZIP
,SA_SITE_City
,SA_SITE_State
,SA_SITE_CRRT
,SA_SITE_PLUS_4
,SR_SITE_ADDR_RAW
,SA_MAIL_HOUSE_NBR
,SA_MAIL_FRACTION
,SA_MAIL_DIR
,SA_MAIL_STREET_NAME
,SA_MAIL_SUF
,SA_MAIL_POST_DIR
,SA_MAIL_UNIT_PRE
,SA_MAIL_UNIT_VAL
,SA_MAIL_CITY_STATE
,SA_MAIL_STATE
,SA_MAIL_ZIP
,SA_SITE_MAIL_SAME
,SA_LGL_DSCRPTN
,SA_TOWNSHIP
,SA_PHONE_NBR
,SA_PRIVACY_CODE
,USE_CODE_MUNI
,USE_CODE_STD
,SA_ZONING
,MM_ASSESSMENT_YEAR
,SA_VAL_ASSD
,SA_VAL_ASSD_IMPRV
,SA_VAL_ASSD_LAND
,SA_IMPRV_PCT
,SA_TAX_VAL
,SA_TAX_YEAR_DELINQ
,SA_VAL_FULL_CASH
,SA_VAL_CURRENT_LIMIT
,SA_VAL_MARKET
,SA_APPRAISE_VAL
,SA_YR_BLT
,SA_YR_BLT_EFFECT
,SA_BLDG_SHAPE_CODE
,SA_ARCHITECTURE_CODE
,SA_STRUCTURE_CODE
,SA_EXTERIOR_1_CODE
,SA_EXTERIOR_2_CODE
,SA_CONSTRUCTION_CODE
,SA_CONSTRUCTION_QLTY
,SA_LOT_DEPTH
,SA_LOT_WIDTH
,SA_LOTSIZE
,SA_SQFT
,SA_SQFT_DQ
,SA_ADDTNS_SQFT
,SA_ATTIC_SQFT
,SA_BSMT_FIN_SQFT
,SA_GRG_SQFT_1
,SA_HEATING_COOLING
,SA_HEATING_DETAIL
,SA_COOLING_DETAIL
,SA_FIREPLACE_CODE
,SA_GARAGE_CARPORT
,SA_NBR_BATH
,SA_NBR_BEDRMS
,SA_NBR_RMS
,SA_NBR_STORIES
,SA_NBR_UNITS
,SA_POOL_CODE
,SA_ROOF_CODE
,SA_VIEW_CODE
,SA_DATE_TRANSFER
,SA_VAL_TRANSFER
,SR_BUYER
,SR_SELLER
,SR_ARMS_LENGTH_FLAG
,SR_TRAN_TYPE
,SR_FULL_PART_CODE
,SR_MULT_APN_FLAG_KEYED
,SR_MULT_PORT_CODE
,SA_DATE_NOVAL_TRANSFER
,AsARMS_LENGTH_FLAG_DFS
,SA_LOAN_VAL_1
,SR_LOAN_VAL_2
,SR_LOAN_VAL_3
,HEDONIC_VALUE
,ASSESSED_VALUE
,RSI_VALUE
,APPR_EMUL_VALUE
,AVM_VALUE
,ReplyAVM
,ReplyScore
,Alpha_Reliability_Score
,RELIABILITY_SCORE
,SA_X_COORD
,SA_Y_COORD
,Longitude
,Latitude
,SA_CENSUS_TRACT
,SA_CENSUS_BLOCK_GROUP
,SA_GEO_QLTY_CODE
,Street_name_soundex
FROM Propertydetails p join propertycenter.dbo.zipcodes z on p.sa_site_zip = z.zipcode
where z.county = ''+@countyname+''
'
print (@sql + @sql2)
set
@sql = ''
set
@sql2 = ''
end
fetch
next from county_name into @countyname
CLOSE
county_name
DEALLOCATE
county_name
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply