May 12, 2022 at 6:02 pm
I have been creating packages to load monthly data sets from an insurance company. I load everything into an "Import" table with everything set to VARCHAR first from the text files. Then I run a second query that does some ETL to convert fields from varchar to dates, money, int, bigint, numeric, etc when it goes into the destination table. I'm not specifying the column name used as the Identity Column (ROW_ID) as part of the Insert Into query. I am wondering if one of my convert or cast lines are bad because if I leave everything as varchar in the destination table, with Identity on Row_ID I don't get the identity error listed below. I'm very confused.
Insert Into DestinationTable
Select
convert(int,[ProcessingYearMonth]) as [ProcessingYearMonth]
,convert(int,[ClaimSource_ID]) as [ClaimSource_ID]
,convert(bigint,[PerMember_ID]) as [PerMember_ID]
,[MemberContractNumber]
,[MemberGroupNumber]
,[MemberFirstName]
,[MemberLastName]
,cast([MemberBirth_Dt] as date) as [MemberBirth_Dt]
,...
From Source_Import_Table
The Destination table has the first column set to Identity, auto increment by 1, starting with 1.
I've done this process for 48 files into 2x other tables with zero issue and I can't figure out why I'm getting this error because I am NOT selecting the identity column (Row_ID) in the Insert Into statement:
An explicit value for the identity column in table 'CLM_BCBSM_MA_PHR' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Here is my complete Create Table statement:
CREATE TABLE [dbo].[CLM_Destination](
[Row_ID] [int] IDENTITY(1,1) NOT NULL,
[ProcessingYearMonth] [int] NULL,
[ClaimSource_ID] [int] NULL,
[PerMember_ID] [bigint] NULL,
[MemberContractNumber] [varchar](9) NULL,
[MemberGroupNumber] [varchar](12) NULL,
[MemberFirstName] [varchar](100) NULL,
[MemberLastName] [varchar](100) NULL,
[MemberBirth_Dt] [date] NULL,
[MemberGender_CD] [varchar](1) NULL,
[MemberRel_CD] [varchar](3) NULL,
[SubscriberFirstName] [varchar](100) NULL,
[SubscriberLastName] [varchar](100) NULL,
[AttributedGroup_ID] [varchar](50) NULL,
[AttributedSubgroup_ID] [varchar](50) NULL,
[AttributedPracticeUnit_id] [varchar](50) NULL,
[AttributedPhysicianFirstName] [varchar](100) NULL,
[AttributedPhysicianLastName] [varchar](100) NULL,
[AttributedPhysicianNPI] [varchar](10) NULL,
[PrescribingPhysicianGroup_ID] [varchar](50) NULL,
[PrescribingPhysicianSubgroup_ID] [varchar](50) NULL,
[PrescribingPhysicianPracticeUnit_id] [varchar](50) NULL,
[PrescribingPhysicianFirstName] [varchar](100) NULL,
[PrescribingPhysicianLastName] [varchar](100) NULL,
[PrescribingPhysicianNPI] [varchar](10) NULL,
[PrescribingPhysicianSpecialty] [varchar](255) NULL,
[Prescriber_ID] [varchar](20) NULL,
[PrescriptionNumber] [varchar](20) NULL,
[Purchase_DT] [date] NULL,
[NationalDrug_CD] [varchar](20) NULL,
[TradeName] [varchar](30) NULL,
[DosageForm_CD] [varchar](10) NULL,
[GenericClassName] [varchar](30) NULL,
[Generic_CD] [varchar](20) NULL,
[Generic_IND] [varchar](20) NULL,
[GenericProduct_CD] [varchar](20) NULL,
[DailySupplyNumber] [decimal](5, 3) NULL,
[Quantity] [decimal](18, 3) NULL,
[RefillNumber] [bigint] NULL,
[FormularyStatus_CD] [varchar](20) NULL,
[SpecialDrugCategory_CD] [varchar](20) NULL,
[DrugDispensationType_CD] [varchar](20) NULL,
[DrugStrengthQuantity] [varchar](50) NULL,
[MailRetail_CD] [varchar](20) NULL,
[CompoundDrug_CD] [varchar](20) NULL,
[StandardTherapeuticClass_CD] [varchar](20) NULL,
[StandardTherapeuticClass_DESC] [varchar](255) NULL,
[AHFSTherapeuticClass_CD] [varchar](20) NULL,
[AHFSTherapeuticClass_DESC] [varchar](255) NULL,
[SpecificTherapeuticClass_CD] [varchar](20) NULL,
[SpecificTherapeuticClass_DESC] [varchar](255) NULL,
[StandardCost] [money] NULL,
[AllowedAmount] [money] NULL
)
And here is my complete Insert Into Statement.
insert into [CLM_Destination]
SELECT
convert(int,[ProcessingYearMonth]) as [ProcessingYearMonth]
,convert(int,[ClaimSource_ID]) as [ClaimSource_ID]
,convert(bigint,[PerMember_ID]) as [PerMember_ID]
,[MemberContractNumber]
,[MemberGroupNumber]
,[MemberFirstName]
,[MemberLastName]
,cast([MemberBirth_Dt] as date) as [MemberBirth_Dt]
,[MemberGender_CD]
,[MemberRel_CD]
,[SubscriberFirstName]
,[SubscriberLastName]
,[AttributedGroup_ID]
,[AttributedSubgroup_ID]
,[AttributedPracticeUnit_id]
,[AttributedPhysicianFirstName]
,[AttributedPhysicianLastName]
,[AttributedPhysicianNPI]
,[PrescribingPhysicianGroup_ID]
,[PrescribingPhysicianSubgroup_ID]
,[PrescribingPhysicianPracticeUnit_id]
,[PrescribingPhysicianFirstName]
,[PrescribingPhysicianLastName]
,[PrescribingPhysicianNPI]
,[PrescribingPhysicianSpecialty]
,[Prescriber_ID]
,[PrescriptionNumber]
,cast([Purchase_DT] as date) [Purchase_DT]
,[NationalDrug_CD]
,[TradeName]
,[DosageForm_CD]
,[GenericClassName]
,[Generic_CD]
,[Generic_IND]
,[GenericProduct_CD]
,convert(decimal(5,0),[DailySupplyNumber]) as [DailySupplyNumber]
,convert(decimal(18,3),[Quantity]) as [Quantity]
,convert(bigint,[RefillNumber]) as [RefillNumber]
,[RefillNumber]
,[FormularyStatus_CD]
,[SpecialDrugCategory_CD]
,[DrugDispensationType_CD]
,[DrugStrengthQuantity]
,[MailRetail_CD]
,[CompoundDrug_CD]
,[StandardTherapeuticClass_CD]
,[StandardTherapeuticClass_DESC]
,[AHFSTherapeuticClass_CD]
,[AHFSTherapeuticClass_DESC]
,[SpecificTherapeuticClass_CD]
,[SpecificTherapeuticClass_DESC]
,convert(money,[StandardCost]) as [StandardCost]
,convert(money,[AllowedAmount]) as [AllowedAmount]
FROM [PayorData].[dbo].[CLM_Import]
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
May 12, 2022 at 6:19 pm
The error message, somewhat misleadingly, describes the problem -- you need to specify the columns in the insert statement if not inserting all columns corresponding to every column in the SELECT statement (in the same order). Otherwise, the SQL engine assumes you are inserting the selected columns into first N columns of the inserted table. e.g.,
Insert Into DestinationTable
([ProcessingYearMonth], [ClaimSource_ID], [PerMember_ID], [MemberContractNumber], [MemberGroupNumber], [MemberFirstName], [MemberLastName], [MemberBirth_Dt], ...)
Select
convert(int,[ProcessingYearMonth]) as [ProcessingYearMonth]
,convert(int,[ClaimSource_ID]) as [ClaimSource_ID]
,convert(bigint,[PerMember_ID]) as [PerMember_ID]
,[MemberContractNumber]
,[MemberGroupNumber]
,[MemberFirstName]
,[MemberLastName]
,cast([MemberBirth_Dt] as date) as [MemberBirth_Dt]
,...
From Source_Import_Table
May 12, 2022 at 8:24 pm
THANK YOU! I wanted all rows of data from ALL columns in my "Import" table to be inserted into my "Destination" table, I had a duplicate column name due to a copy/paste mistake!! Found it and fixed it. My error was below.
,convert(bigint,[RefillNumber]) as [RefillNumber]
,[RefillNumber]
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply