how to update a table based on criteria being true in another table

  • In using a access routine, I updated the test instead of the live. I do not have the criteria any longer about which records where updated except for in the test table.

    My boss wants me to write a scipt that based on the criteria of the UnderWritingString column in a table in test , update the live table's underwriting string.

    Never did this before:

    Am I on the right track, does this have hope?

    SELECT CertificateNrClean FROM Test.dbo.Application

    WHERE UnderwritingString like '%UW-R-Photo_-3;%'

    IF Exists(SELECT UnderwritingString FROM Live.dbo.Application

    WHERE UnderwritingString not like '%UW-R-Photo_-3;%') BEGIN

    UPDATE Live.dbo.Application

    SET UnderwritingString = UnderwritingString + 'UW-R-Photo_-3;'

    Where Live.dbo.Application.CertificateNrClean = Test.dbo.Application.CertificateNrClean

    END

  • Are the Databases in two different servers?

    If same you update using Alias.

    Update a

    set a.UnderwritingString = a.UnderwritingString + 'UW-R-Photo_-3;'

    from Live.dbo.Application as a

    JOIN

    Test.dbo.Application as b

    ON a.CertificateNrClean = b.CertificateNrClean

    But please test it out in a test environment.

    or Do a select with the same from and where clause and see if you are getting the right results before the actual update.

    Also take a back up of the Live table before you run the update

    -Roy

  • DO I need a cursor?

    Where Live.dbo.Application.CertificateNrClean = Test.dbo.Application.CertificateNrClean

    Error says a column in the Where cannot be bound. hhmm

    DO I need to put it into a variable when selecting from test DB table, and if so won't I need a cusror to run through all rows' certificateNrClean values?

  • So the Where clause you have is not a primary key?

    -Roy

  • NO. Thanks I will try your suggestion now.

    CREATE TABLE [dbo].[Application](

    [ApplicationID] [int] IDENTITY(1,1) NOT NULL,

    [Complete] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Complete] DEFAULT ((0)),

    [TransactionType] [varchar](2) NOT NULL,

    [StatusActive] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_StatusActive] DEFAULT ((0)),

    [Program] [varchar](5) NOT NULL,

    [WindCatastrophe] [varchar](1) NOT NULL,

    [EnteredBy] [varchar](10) NOT NULL,

    [ContractNr] [varchar](20) NOT NULL,

    [CertificateNr_Old] [varchar](23) NOT NULL,

    [CertificateNr] [varchar](23) NOT NULL,

    [CertificateNrClean] [varchar](14) NOT NULL,

    [Ins_CompanyCode] [varchar](2) NOT NULL,

    [ContractCode] [varchar](3) NOT NULL,

    [ProgramCode] [varchar](2) NOT NULL,

    [CoreNr] [varchar](5) NOT NULL,

    [EndorseNr] [varchar](2) NOT NULL,

    [TermYear] [varchar](2) NOT NULL,

    [TermOrigin] [varchar](2) NOT NULL,

    [TermBegin] [smalldatetime] NOT NULL,

    [TermEnd] [smalldatetime] NOT NULL,

    [PolicyBegin] [smalldatetime] NOT NULL,

    [PolicyEnd] [smalldatetime] NOT NULL,

    [RewriteFrom] [varchar](23) NOT NULL,

    [RewrittenAS] [varchar](23) NOT NULL,

    [RewriteToContractCode] [varchar](3) NOT NULL CONSTRAINT [DF_ApplicationNew_RewriteToContractCode] DEFAULT ('N/A'),

    [ProcessingDate] [smalldatetime] NOT NULL,

    [SysUpdateTime] [smalldatetime] NULL,

    [NonRenewReason] [varchar](25) NOT NULL,

    [NonRenewedBy] [varchar](10) NOT NULL,

    [NonRenewedDate] [smalldatetime] NOT NULL,

    [UnderwritingString] [varchar](250) NOT NULL CONSTRAINT [DF_ApplicationNew_UnderwritingString] DEFAULT (''),

    [ClaimsHistoryString] [varchar](250) NOT NULL CONSTRAINT [DF_ApplicationNew_ClaimsHistoryString] DEFAULT (''),

    [CommissionRateAgent] [money] NOT NULL CONSTRAINT [DF_Application_CommissionRateAgent] DEFAULT ((0)),

    [CommissionRateContract] [money] NOT NULL CONSTRAINT [DF_Application_CommissionRateContract] DEFAULT ((0)),

    [CovI] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovI] DEFAULT ((0)),

    [CovII] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovII] DEFAULT ((0)),

    [CovIII_LU] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovIII_LU] DEFAULT ((0)),

    [CovIV] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovIV] DEFAULT ((0)),

    [CovLossAssessment_NonHO6] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovLossAssessment_NonHO6] DEFAULT ((0)),

    [CovTheftOnPremises] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovTheftOnPremises] DEFAULT ((0)),

    [CovEPersonalLiability] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovEPersonalLiab] DEFAULT ((0)),

    [CovFMedPay] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovFMedPay] DEFAULT ((0)),

    [CovOrdinanceLaw] [decimal](9, 6) NOT NULL CONSTRAINT [DF_ApplicationNew_CovOrdinanceLaw] DEFAULT ((0)),

    [CovWaterDamage] [money] NOT NULL,

    [CovPersArticleFloater] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovPersArticleFloater] DEFAULT ((0)),

    [CovPersonalInjury] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_CovPersInjury] DEFAULT ((0)),

    [CovReplacementCostStructures] [int] NOT NULL CONSTRAINT [DF_ApplicationNew_CovReplacementCostStructures] DEFAULT ((0)),

    [CovReplacementCostContents] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_CovReplacementCostContents] DEFAULT ((0)),

    [CovWDR] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_CovWDR] DEFAULT ((0)),

    [CovAnimalLiability] [int] NOT NULL CONSTRAINT [DF_ApplicationNew_CovAnimalLiability] DEFAULT ((0)),

    [CovInflationGuard] [smallint] NOT NULL CONSTRAINT [DF_ApplicationNew_CovInflationGuard] DEFAULT ((0)),

    [CovWaterBackUp] [int] NOT NULL CONSTRAINT [DF_ApplicationNew_CovWaterBackUp] DEFAULT ((0)),

    [CovOptLossAssessment] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptLossAssessment] DEFAULT ((0)),

    [CovOptLossOfUse] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptLossOfUse] DEFAULT ((0)),

    [CovOptOtherStructures] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptOtherStructures] DEFAULT ((0)),

    [CovOptTheft] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptTheft] DEFAULT ((0)),

    [CovOptPersonalLiability] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptPersLiab] DEFAULT ((0)),

    [CovOptMedPay] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptMedPay] DEFAULT ((0)),

    [CovOptAddInsured] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptAddInsured] DEFAULT ((0)),

    [CovOptOrdinanceLaw] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptOrdinanceLaw] DEFAULT ((0)),

    [CovOptWaterDamage] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptWaterDamage] DEFAULT ((0)),

    [CovOptPersArticleFloater] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptPersArticleFloater] DEFAULT ((0)),

    [CovOptPersonalInjury] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptPersInjury] DEFAULT ((0)),

    [CovOptReplacementCostStructures] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptReplacementCostStructures] DEFAULT ((0)),

    [CovOptReplacementCostContents] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptReplacementCostContents] DEFAULT ((0)),

    [CovOptWDR] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptWDR] DEFAULT ((0)),

    [CovOptAnimalLiability] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptAnimalLiability] DEFAULT ((0)),

    [CovOptInflationGuard] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptInflationGuard] DEFAULT ((0)),

    [CovOptWaterBackUp] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptWaterBackUp] DEFAULT ((0)),

    [CovOptDedAOP] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptDedAOP] DEFAULT ((0)),

    [CovOptDedEQ] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptDedEQ] DEFAULT ((0)),

    [CovOptDedFlood] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptDedFlood] DEFAULT ((0)),

    [CovOptDedWaterDamage] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptDedWaterDamage] DEFAULT ((0)),

    [CovOptDedWind] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_CovOptDedWind] DEFAULT ((0)),

    [CovOptComments] [varchar](1000) NOT NULL,

    [DedAOP] [varchar](5) NOT NULL CONSTRAINT [DF_ApplicationNew_DedAOP] DEFAULT ('-1'),

    [DedEQ] [varchar](5) NOT NULL CONSTRAINT [DF_ApplicationNew_DedEQ] DEFAULT ('-1'),

    [DedFlood] [varchar](5) NOT NULL CONSTRAINT [DF_ApplicationNew_DedFlood] DEFAULT ('-1'),

    [DedWind] [varchar](5) NOT NULL CONSTRAINT [DF_ApplicationNew_DedWind] DEFAULT ('-1'),

    [DedWaterDamage] [varchar](5) NOT NULL CONSTRAINT [DF_ApplicationNew_DedWaterDamage] DEFAULT ((2500)),

    [DebitCreditProtectionClass] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditProtClass] DEFAULT ((0)),

    [DebitCreditSelections] [varchar](255) NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditSelections] DEFAULT (''),

    [DebitCreditAmount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditUnderwriting] DEFAULT ((0)),

    [DebitCreditHurricaneShutters] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditHurricaneShut] DEFAULT ((0)),

    [DebitCreditAlarm] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditAlarm] DEFAULT ((0)),

    [DebitCreditRentalType] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_DebitCreditRentalType] DEFAULT ((0)),

    [PremiumI] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumI] DEFAULT ((0)),

    [PremiumII] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumII] DEFAULT ((0)),

    [PremiumOptions] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumOptions] DEFAULT ((0)),

    [SubTotal] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_SubTotal] DEFAULT ((0)),

    [PolicyFee] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PolicyFee] DEFAULT ((0)),

    [OtherFeeSelections] [varchar](255) NOT NULL CONSTRAINT [DF_ApplicationNew_OtherFeeSelections] DEFAULT (''),

    [OtherFee] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherFee_1] DEFAULT ((0)),

    [TaxableTotal] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_TaxableTotal] DEFAULT ((0)),

    [SurplusLinesTax] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_SurplusLinesTax] DEFAULT ((0)),

    [SurplusLinesRate] [decimal](9, 6) NOT NULL CONSTRAINT [DF_ApplicationNew_SurplusLinesRate] DEFAULT ((0)),

    [OtherTax1] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax1] DEFAULT ((0)),

    [OtherTax1Rate] [decimal](9, 6) NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax1Rate] DEFAULT ((0)),

    [OtherTax2] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax2] DEFAULT ((0)),

    [OtherTax2Rate] [decimal](9, 6) NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax2Rate] DEFAULT ((0)),

    [OtherTax3] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax3_1] DEFAULT ((0)),

    [OtherTax3Rate] [decimal](9, 6) NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax3Rate_1] DEFAULT ((0)),

    [PremiumTotal] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumTotal] DEFAULT ((0)),

    [PremiumReceived] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumReceived] DEFAULT ((0)),

    [PropertyTIV] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PropertyTIV] DEFAULT ((0)),

    [Comments] [varchar](500) NOT NULL,

    [Canceled] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Canceled] DEFAULT ((0)),

    [Endorsed] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Endorsed] DEFAULT ((0)),

    [ReInstated] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_ReInstated] DEFAULT ((0)),

    [ReNewed] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_ReNewed] DEFAULT ((0)),

    [InternalRenewal] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_RenewalInProcess] DEFAULT ((0)),

    [EN_CovI_Old] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_CovI_Old] DEFAULT ((0)),

    [EN_CovII_Old] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_CovII_Old] DEFAULT ((0)),

    [EN_CovIII_LU_Old] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_CovIII_LU_Old] DEFAULT ((0)),

    [EN_CovIV_Old] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_CovIV_Old] DEFAULT ((0)),

    [EN_PremiumI_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_Premium1_Annual] DEFAULT ((0)),

    [EN_PremiumII_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_PremiumII_Annual] DEFAULT ((0)),

    [EN_PremiumOptions_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_PremiumOpt_Annual] DEFAULT ((0)),

    [EN_SubTotal_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_SubTotal_Annual] DEFAULT ((0)),

    [EN_PolicyFee_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_PolicyFee_Annual] DEFAULT ((0)),

    [EN_OtherFee_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_OtherFee_Annual_1] DEFAULT ((0)),

    [EN_TaxableTotal_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_TaxableTotal_Annual] DEFAULT ((0)),

    [EN_SurplusLinesTax_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_Surpluslines_Annual] DEFAULT ((0)),

    [EN_OtherTax1_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_OtherTax1_Annual] DEFAULT ((0)),

    [EN_OtherTax2_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_OtherTax2_Annual] DEFAULT ((0)),

    [EN_OtherTax3_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_OtherTax3_Annual_1] DEFAULT ((0)),

    [EN_PremiumTotal_Annual] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_EN_PremiumTotal_Annual] DEFAULT ((0)),

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

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

    [PremiumI_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumI_Amount] DEFAULT ((0)),

    [PremiumII_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumII_Amount] DEFAULT ((0)),

    [PremiumOptions_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumOpt_Amount] DEFAULT ((0)),

    [SubTotal_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_SubTotal_Amount] DEFAULT ((0)),

    [PolicyFee_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PolicyFee_Amount] DEFAULT ((0)),

    [OtherFee_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherFee_Amount] DEFAULT ((0)),

    [TaxableTotal_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_TaxableTotal_Amount] DEFAULT ((0)),

    [SurplusLinesTax_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_SurplusLineTax_Amount] DEFAULT ((0)),

    [OtherTax1_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax1_Amount] DEFAULT ((0)),

    [OtherTax2_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax2_Amount] DEFAULT ((0)),

    [OtherTax3_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_OtherTax3_Amount] DEFAULT ((0)),

    [PremiumTotal_Amount] [money] NOT NULL CONSTRAINT [DF_ApplicationNew_PremiumTotal_Amount] DEFAULT ((0)),

    [Cust_Type] [varchar](1) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Type] DEFAULT ('I'),

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

    [Cust_Prefix] [varchar](8) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Prefix] DEFAULT (''),

    [Cust_FirstName] [varchar](30) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_FirstName] DEFAULT (''),

    [Cust_Suffix] [varchar](3) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Suffix] DEFAULT (''),

    [Cust_Name2] [varchar](30) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Name2] DEFAULT (''),

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

    [Cust_City] [varchar](30) NOT NULL,

    [Cust_State] [varchar](2) NOT NULL,

    [Cust_Zip] [varchar](10) NOT NULL,

    [Cust_Providence] [varchar](30) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Providence] DEFAULT (''),

    [Cust_Country] [varchar](30) NOT NULL,

    [Cust_Phone1] [varchar](10) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Phone1] DEFAULT (''),

    [Cust_Phone2] [varchar](10) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Phone2] DEFAULT (''),

    [Cust_Fax] [varchar](10) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Fax] DEFAULT (''),

    [Cust_CellPhone] [varchar](10) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_CellPhone] DEFAULT (''),

    [Cust_Email] [varchar](50) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Email] DEFAULT (''),

    [Cust_SSN] [varchar](9) NOT NULL,

    [Cust_DateOfBirth] [smalldatetime] NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_DateOfBirth] DEFAULT (((1)/(1))/(1900)),

    [Cust_Employer] [varchar](30) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_Employer] DEFAULT (''),

    [Cust_TimeCurEmplr] [varchar](15) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_TimeCurEmplr] DEFAULT (''),

    [Cust_PrevEmplr] [varchar](30) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_PrevEmplr] DEFAULT (''),

    [Cust_TimePrevEmplr] [varchar](15) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_TimePrevEmplr] DEFAULT (''),

    [Cust_TimeCurRes] [varchar](15) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_TimeCurRes] DEFAULT (''),

    [Cust_TimePrevRes] [varchar](15) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_TimePrevRes] DEFAULT (''),

    [Cust_AdditionalInterest] [varchar](1000) NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_AdditionalInterest] DEFAULT (''),

    [Cust_AdditionalInterestPayableNr] [tinyint] NOT NULL CONSTRAINT [DF_ApplicationNew_Cust_AdditionalInterestPayableNr] DEFAULT ((0)),

    [Loc_AssociationName] [varchar](30) NOT NULL,

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

    [Loc_UnitNr] [varchar](10) NOT NULL,

    [Loc_City] [varchar](30) NOT NULL,

    [Loc_State] [varchar](2) NOT NULL,

    [Loc_Zip] [varchar](5) NOT NULL,

    [Loc_County] [varchar](30) NOT NULL,

    [Loc_Country] [varchar](30) NOT NULL,

    [Loc_AddressSame] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_AddressSame] DEFAULT ((0)),

    [Loc_ConstructionType] [varchar](30) NOT NULL,

    [Loc_FloodZone] [varchar](1) NOT NULL,

    [Loc_LavaZone] [varchar](1) NOT NULL,

    [Loc_ProtectionClass] [varchar](2) NOT NULL,

    [Loc_Territory] [varchar](10) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_Territory] DEFAULT (''),

    [Loc_WaterDistance] [varchar](10) NOT NULL,

    [Loc_WindPool] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_WindPool] DEFAULT ((0)),

    [Loc_BuildingType] [varchar](10) NOT NULL,

    [Loc_NrOfStories] [varchar](3) NOT NULL,

    [Loc_GroundFloor] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_GroundFloor] DEFAULT ((0)),

    [Loc_FloorNr] [varchar](3) NOT NULL,

    [Loc_YearConstructed] [varchar](4) NOT NULL,

    [Loc_SqFeet] [varchar](5) NOT NULL,

    [Loc_OccupancyType] [varchar](10) NOT NULL,

    [Loc_OccupantsInfo] [varchar](10) NOT NULL,

    [Loc_RentalType] [varchar](1) NOT NULL,

    [Loc_MonthlyRent] [money] NOT NULL,

    [Loc_AlarmSystem] [varchar](10) NOT NULL,

    [Loc_BusinessOnPremises] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_BusinessOnPremises] DEFAULT ((0)),

    [Loc_SwimmingPool] [varchar](3) NOT NULL,

    [Loc_WoodStove] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_WoodStove] DEFAULT ((0)),

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

    [Loc_PrevLossDescription] [varchar](250) NOT NULL,

    [Loc_PrevTheftLoss] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_PrevTheftLoss] DEFAULT ((0)),

    [Loc_PrevTheftCurAddr] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_PrevTheftCurAddr] DEFAULT ((0)),

    [Loc_UpdateElectric] [varchar](4) NOT NULL,

    [Loc_UpdatePlumbing] [varchar](4) NOT NULL,

    [Loc_UpdateHvac] [varchar](4) NOT NULL,

    [Loc_UpdateRoof] [varchar](4) NOT NULL,

    [Loc_RoofType] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_RoofType] DEFAULT (''),

    [Loc_RoofCover] [varchar](1) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_RoofCover] DEFAULT (''),

    [Loc_ResistiveDevice_RoofToWall] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_ResistiveDevice_RoofToWall] DEFAULT (''),

    [Loc_ResistiveDevice_WallToFoundation] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_ResistiveDevice_WallToFoundation] DEFAULT (''),

    [Loc_ResistiveDevice_Opening] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_ResistiveDevice_Opening] DEFAULT (''),

    [Loc_ResistiveDevice_Fire] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_ResistiveDevice_Fire] DEFAULT (''),

    [Loc_Foundation] [varchar](2) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_Foundation] DEFAULT (''),

    [Loc_GatedCommunity] [varchar](1) NOT NULL CONSTRAINT [DF_ApplicationNew_Loc_GatedCommunity] DEFAULT ('N'),

    [Mortgagee1_ID] [int] NOT NULL,

    [LoanNr] [varchar](25) NOT NULL,

    [ClosingDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ApplicationNew_ClosingDate] DEFAULT (((1)/(1))/(1900)),

    [InvoiceMortg] [bit] NOT NULL CONSTRAINT [DF_ApplicationNew_InvoiceMortg] DEFAULT ((0)),

    [Mortgagee2_ID] [int] NOT NULL,

    [LoanNr2] [varchar](25) NOT NULL,

    [AgencyID] [int] NOT NULL,

    [AgentID] [int] NOT NULL,

    [WebApplicationID] [int] NOT NULL CONSTRAINT [DF_ApplicationNew_WebApplicationID] DEFAULT ((0)),

    CONSTRAINT [pk_Application] PRIMARY KEY NONCLUSTERED

    (

    [ApplicationID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

    CONSTRAINT [ix_CertificateNr] UNIQUE NONCLUSTERED

    (

    [CertificateNr] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

  • Thanks so much, it works! I would like to know the syntax if the tables where on different servers? how would I bound those.

    From

    different Instances on same box:

    Instance1Name.DBName.dbo.Table1.Col1 ??

    AND

    Instance2Name.DBName.dbo.Table2.Col2 ??

    different servers:

    Join

    server1.DBName.dbo.Table1.Col1 ??

    AND

    server2.DBName.dbo.Table2.Col2 ??

  • You should look up LinkedServer on BOL. That will give you all the details you need. (sp_addlinkedserver)

    -Roy

  • Super thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply