July 6, 2006 at 4:33 pm
I need to create 52 databases really quick and am having trouble with this insert statement..
What am I missing
Insert Into PropertyDetails(
[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]
,[Alpha_Reliability_Score]
,[RELIABILITY_SCORE]
,[SA_X_COORD]
,[SA_Y_COORD]
,[SA_CENSUS_TRACT]
,[SA_CENSUS_BLOCK_GROUP]
,[SA_GEO_QLTY_CODE]
,[FILLER]
,[Longitude]
,[Latitude]
,[Reply10Pct]
)
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]
,[Alpha_Reliability_Score]
,[RELIABILITY_SCORE]
,[SA_X_COORD]
,[SA_Y_COORD]
,[SA_CENSUS_TRACT]
,[SA_CENSUS_BLOCK_GROUP]
,[SA_GEO_QLTY_CODE]
,[FILLER]
,[SA_X_COORD] / cast(-1000000 as float)
,[SA_Y_COORD] / cast(1000000 as float)
,[AVM_Value] + ([AVM_Value * .1) as [Reply10Pct]
From AllPropertyDetails
Where [USE_CODE_STD] in ('RCON', 'RCOO', 'RDUP', 'RMSC', 'RMOB', 'RMFD', 'RAPT', 'RQUA', 'RSFR', 'RTIM', 'RTRI', 'MUTI')
July 6, 2006 at 4:36 pm
Figured it out - needed a ]
,[AVM_Value] + ([AVM_Value] * .1) as [Reply10Pct]
From AllPropertyDetails
Thanks though
July 6, 2006 at 5:03 pm
What is really missing here is database design.
_____________
Code for TallyGenerator
July 7, 2006 at 3:31 am
If possible Normalize your database
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply