Insert records into DM Dim tables

[RonFusionHSLLC]

Is there any best practice to insert records into dim tables...directly.   They would never conflict, as they all have negative OID values (-2,-3...etc)

I do not want these records in the landing zone (ODS).  I don't care if they are in the DW, for instance add them during the staging process.  They would be duplicated each time, not a problem given there are only 3 or 4 records in 6 dims.

Something like this is what I was planning

INSERT INTO dbo.[TDMA_1Dim_CInsPlan]
(CInsPlan_FD, CInsPlan_TD, CInsPlan_OID, ID_InsPlan, Alias, [Name], Address1, Address2, City, State, Zip, Zip4, Contact, Phone, Extension, Fax, Email, Web, Notes, PayerID, EntryDt, EntryBy, LastModDt, LastModBy, SubscriberNumMask, MemberNumMask, GroupNumMask, PrimaryElectronic, NonPrimaryElectronic, ClearingHouseID, InsuranceType, ClaimFilingIndicator, TaxonomyHeaderID, NarrativeRecord, PrimaryPaperFormat, NonPrimaryPaperFormat, ZeroFeeOnClaim, AutoCrossOver, OverrideBilling, OverrideExpected, GuarantorStatement, InfoStmtOnPrimary, InfoStmtOnNonPrimary, ProvOrgNumber, ProviderNumber, RefProvNumber, FacilityNumber, ECCPayerID, ECCOfficeID, MedigapID, MedigapExtension, PseudoCode, ConcurrencySchemeID, TeamBillingOption, PrintASACodeOnAnesthesiaClaim, UseASACodeOnExpectedAllowed, ExtremeAgeCode, EnableFollowUp, FirstFollowUpActionAfter, SendUnpaidXOverClaimsAfter, PaymentActionSchemeID, ExpiryDt, UBClaimFormat, FinancialClassID, AddressType, CountryCode, PostalCode, CountySubDivisionCode, StatInqClaimFormat, EligibilityID, ICDVersionInstitutional, ICDVersionProfessional, PQRS, BillingAlert, PrimaryECPQRSAmount, EligibilityNPI, AfterHoursCode, NonPrimaryECPQRSAmount, PrimaryElectronicInstitutional, NonPrimaryElectronicInstitutional, ElectronicInterfaceInstitutional, NAICCode, CarrierCode, AttendingProvSpecialty2310APRV, BillingProvSpecialty2000APRV, TaxStatus, MACAnesthesiaCode, ContractCode, ProvOrgClaimRemittanceAddress, TeamBillingOption_Desc, AddressType_Desc, TaxStatus_Desc, INS_CATEGORY, INS_SUBCATEGORY, OBSOLETE__INDICATION, CInsPlan_RUNNO_INSERT, CInsPlan_RUNNO_UPDATE)
VALUES ( '01/01/1780',
'12/31/9999',
-2,
-2,
'SELFPAY',
'SELFPAY',

[shashi_holla · Accepted solution]

Hi @RonFusionHSLLC 

No issues, please go ahead. As a best practice, as part of Post Loading ETL check if they exist, if not then insert them.

Thank you, 

[RonFusionHSLLC]

Perfect, thanks Shashi, I'll change my insert to a MERGE.