October 1, 2009 at 10:04 am
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
October 1, 2009 at 10:13 am
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
October 1, 2009 at 10:15 am
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?
October 1, 2009 at 10:15 am
So the Where clause you have is not a primary key?
-Roy
October 1, 2009 at 10:51 am
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
October 1, 2009 at 12:19 pm
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 ??
October 1, 2009 at 12:36 pm
You should look up LinkedServer on BOL. That will give you all the details you need. (sp_addlinkedserver)
-Roy
October 2, 2009 at 8:32 am
Super thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply