DTS package help
-
i created a DTS package which imports data from a tab delimited text file into a MSSQL table. it works well if the destination table is empty but if there are some records in it and i run the DTS package - idont get any errors but it doesnt import any data from the text file.
ultimately what I would like to have happen is to have the DTS package check for the records in the text file tosee if they are already in the destination tableand if so,thenskip those records. so that it only imports records that are not already in the database table.
the first field in the destination table is a unique identifier set as PK.
can someone suggest how icoudl do this? i am brand new to DTS package creation so there may be a simple way to do this that i just have nto figured out yet.
-
create a staging table that matches the table you ultimately whant to import to, truncate this table at the begining of the dts using the execute sql task.
After the truncate import the text file into the staging table, again use another execute sql task, and this time do a an insert into your main table using a select from the staging table with a id not in table clause.
-
great! i will give that a try. one quick question though - is there a quick and easy way to dublicate a table structure into a new table with a different name?
-
--
-
right click table in EM and paste into qa, then simply rename the table in the create statement
-
thats a pretty cool trick. although when i ran it in QA it gave the following message:
Warning: The table 'erehab_data_temp' has been created but its maximum row size (39851) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I'm not entirely sure what this means or if it is really a problem.
the TSQL that was pasted to create the table is as follows:
CREATE TABLE [erehab_data_temp] (
[AssessUniqueID] [float] NOT NULL ,
[AssessType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Completed] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Locked] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accepted] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filed] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssessCreated] [smalldatetime] NULL ,
[AssessModified] [smalldatetime] NULL ,
[MedicareNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedicaidNumber] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatIDNumber] [float] NULL ,
[BirthDate] [smalldatetime] NULL ,
[SSN] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthAmerInd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthAsian] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthAfrAmer] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthHispLatino] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthPacIsland] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceEthWhite] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaritalStatus] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreHospitalZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitDate] [smalldatetime] NULL ,
[AssessRefDate] [smalldatetime] NULL ,
[AdmitClass] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFrom] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreHospitalLivingSetting] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreHospitalLivingWith] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreHospitalVocationCat] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreHospitalVocationEffort] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaySourcePrimary] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaySourceSecondary] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitImpGroup] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischImpGroup] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Diagnosis] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OnsetOfDiag] [smalldatetime] NULL ,
[Comorbidity0] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity5] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity6] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity7] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity8] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comorbidity9] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ComatoseAtAdmit] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeleriousAtAdmit] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitSwallowingStat] [float] NULL ,
[DischSwallowingStat] [float] NULL ,
[AdmitDehydration] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischDehydration] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModBladderLvlAssist] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModBladderFreqAccidents] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModBowelLvlAssist] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModBowelFreqAccidents] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModTubTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModShowerTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModDistWalked] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModDistWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModWalk] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFnModWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModBladderLvlAssist] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModBladderFreqAccidents] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModBowelLvlAssist] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModBowelFreqAccidents] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModTubTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModShowerTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModDistWalked] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModDistWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModWalk] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFnModWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMEating] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMGrooming] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMBathing] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMDressingUpper] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMDressingLower] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMToileting] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMBladderCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMBowelCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMBedTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMToiletTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMTubTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMWalkWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMWalkWheelchairMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMStairs] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMComprehension] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMComprehensionMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMExpression] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMExpressionMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMSocialInteraction] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMProblemSolving] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitFIMMemory] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMEating] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMGrooming] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMBathing] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMDressingUpper] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMDressingLower] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMToileting] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMBladderCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMBowelCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMBedTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMToiletTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMTubTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMWalkWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMWalkWheelchairMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMStairs] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMComprehension] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMComprehensionMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMExpression] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMExpressionMeasured] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMSocialInteraction] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMProblemSolving] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischFIMMemory] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMEating] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMGrooming] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMBathing] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMDressingUpper] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMDressingLower] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMToileting] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMBladderCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMBowelCtrl] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMBedTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMToiletTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMTubTransfer] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMWalkWheelchair] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMStairs] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMComprehension] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMExpression] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMSocialInteraction] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMProblemSolving] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GoalFIMMemory] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeDate] [smalldatetime] NULL ,
[DischargeAgainstAdvice] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramInterruptions] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransferDate1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReturnDate1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransferDate2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReturnDate2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransferDate3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReturnDate3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeLivingSetting] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeHomeHealthSvc] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischargeLivingWith] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransferDiagnosis] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication0] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication1] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication2] [nvarchar] (53) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Complication5] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitShortBreathExert] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischShortBreathExert] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitShortBreathRest] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischShortBreathRest] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitCoughDifficulty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischCoughDifficulty] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitHighestPain] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischHighestPain] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitHighestUlcerStage] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischHighestUlcerStage] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitNumberUlcers] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischNumberUlcers] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitLargestUlcerArea] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischLargestUlcerArea] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitExudateAmount] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischExudateAmount] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitTissueType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischTissueType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitTotalPUSHScore] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischTotalPUSHScore] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitBalanceProblem] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischBalanceProblem] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DischNumberFalls] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age_at_Admit] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOS] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Admit_FIM_Total_Motor] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Admit_FIM_Total_Cog] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Disch_FIM_Total_Motor] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Disch_FIM_Total_Cog] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIM_Gain_Motor] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIM_Gain_Cog] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIM_Gain_Total] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIM_ChangePerDay] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMG] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tier] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HIPPSCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShortStayExpired] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShortStayCMG] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransferPatient] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LongStay] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BasePayWeight] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FacAdjPayWeight] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FacilityAdjPayment] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Compliant75PctRule] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Presumptive75PctRule] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMSTransmitDate] [smalldatetime] NULL ,
[PHYSIAN_NAME] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TELEPHONENUMBER] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUNNYVIEW] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FACILITY] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FollowUpDate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InformationSource] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssessmentMethod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Setting] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LivingWith] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VocationalCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VocationalEffort] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryHealthMaint] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryHealthMaint] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Therapy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagB] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiagF] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMEating] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMGrooming] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMBathing] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMDressingUpper] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMDressingLower] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMToileting] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMBladderMgmt] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMBowelMgmt] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMBedTransfer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMToiletTransfer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMTubTransfer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMWalkWheelchair] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMLocomotionMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMStairs] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMComprehension] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMComprehensionMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMExpression] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMExpressionMode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMSocialInteraction] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMProblemSolving] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIMMemory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_erehab_data] PRIMARY KEY CLUSTERED
(
[AssessUniqueID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
-
ok so i got an Execute SQL task runnign first which empties the staging table.
then it goes to a transform data task which inserts the text file contents into the emptied staging table.
but now i need to add a Execute SQL task which will do a an insert into your main table using a select from the staging table with a id not in table clause. but i am unsure of how the sql would look for this part - can i have 2 SQL statements in one like this:
INSERT INTO table
WHERE SELECT * FROM StagingTable WHERE ID NOT IN LiveTable
sorry i am kind of new to the complex queries and DTS.
-
try this:
INSERT INTO MyProductionTable
SELECT Value_Feilds FROM StagingTable
WHERE StagingTable.id not in (select id from MyProductionTable)
-
that seems to have done the trick - thanks!
now one last trick - I am running this DTS package from a ColdFusion Template in a CFQUERY tag and i would love to be able to output the number of records that were inserted. is there any way to get the DTS package to return that as a query result?
-
Are you able to modify the table structure at all?
As the warning message suggests, if you have more than 8060 bytes in an INSERT or UPDATE operation it WILL fail. Given that the table as defined is 39851 bytes, I think the likelyhood of exceeding 8060 is pretty high.
--------------------
Colt 45 - the original point and click interface
-
yeah i have been able to run the DTS import with no problems so i woudl imagine it shoudl be fine.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply