在使用 SQL Server Management Studio 导入时使用 Float 和 Decimal 数据类型时舍入的 Decimal 值

问：

我是 SQL 编程的新手，并且正在将 SQL Server 16.0.1000.6 与 SSMS 19 预览版 4 一起使用。我正在尝试将 csv 平面文件导入到我的个人桌面 SQL Server 实例中。平面文件中的两列 - （Total_Charges 和 Total_Costs）包含货币值，我希望将它们保留为十进制或数字格式，最多保留两位小数。我尝试将平面文件作为批量插入上传，并使用导入向导将数据类型设置为数字、十进制甚至浮点数，并且运行成功率参差不齐。

我遇到了以下问题：

1. 使用批量插入进行上传时，无论我用于十进制和数值数据类型的精度和小数位数值如何，十进制值都会四舍五入到最接近的整数。当我第一次使用批量插入并使用浮点数据类型上传时，值作为浮点数传输过来，但最近，运行相同的脚本会产生整数值。

2. 当我使用导入向导并将值设置为十进制/数字时，这些值按预期传输，但是当我重复此过程时，它们不再以小数形式进入，而是开始以整数形式返回，如前所述

谁能解释一下为什么我在结果中遇到这些不一致，当我需要导入带有十进制值的数据时，您有什么建议来避免这些问题吗？

我的代表性示例、原始源文件和我使用的脚本/进程如下：

直接链接到源文件：医院住院出院（SPARCS 去识别化）文件：2019

原始来源页面： https://health.data.ny.gov/Health/Hospital-Inpatient-Discharges-SPARCS-De-Identified/4ny4-j5zv

具有代表性的示例 CSV（注意：源文件是从 unix/linux 创建的，并且具有 LR 回车行）：

Hospital Service Area,Hospital County,Operating Certificate Number,Permanent Facility Id,Facility Name,Age Group,Zip Code - 3 digits,Gender,Race,Ethnicity,Length of Stay,Type of Admission,Patient Disposition,Discharge Year,CCSR Diagnosis Code,CCSR Diagnosis Description,CCSR Procedure Code,CCSR Procedure Description,APR DRG Code,APR DRG Description,APR MDC Code,APR MDC Description,APR Severity of Illness Code,APR Severity of Illness Description,APR Risk of Mortality,APR Medical Surgical Description,Payment Typology 1,Payment Typology 2,Payment Typology 3,Birth Weight,Emergency Department Indicator,Total Charges,Total Costs
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,30 to 49,106,M,Other Race,Not Span/Hispanic,2,Emergency,Home or Self Care,2019,INF008,VIRAL INFECTION,,,723,VIRAL ILLNESS,18,INFECTIOUS AND PARASITIC DISEASES (SYSTEMIC OR UNSPECIFIED SITES),2,Moderate,Minor,Medical,Private Health Insurance,,,,true,26507,4773.11
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,105,F,Black/African American,Not Span/Hispanic,3,Emergency,Home or Self Care,2019,GEN002,ACUTE AND UNSPECIFIED RENAL FAILURE,,,469,ACUTE KIDNEY INJURY,11,DISEASES AND DISORDERS OF THE KIDNEY AND URINARY TRACT,2,Moderate,Moderate,Medical,Medicare,,,,true,20693,5631.3
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,50 to 69,105,F,Other Race,Not Span/Hispanic,7,Elective,Home or Self Care,2019,GEN006,OTHER SPECIFIED AND UNSPECIFIED DISEASES OF KIDNEY AND URETERS,GIS005,EXPLORATION OF PERITONEAL CAVITY,443,KIDNEY & URINARY TRACT PROCEDURES FOR NONMALIGNANCY,11,DISEASES AND DISORDERS OF THE KIDNEY AND URINARY TRACT,4,Extreme,Extreme,Surgical,Medicare,Blue Cross/Blue Shield,,,false,138252,29848.69
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,50 to 69,106,F,Black/African American,Not Span/Hispanic,1,Emergency,Left Against Medical Advice,2019,RSP009,ASTHMA,,,141,ASTHMA,04,DISEASES AND DISORDERS OF THE RESPIRATORY SYSTEM,2,Moderate,Minor,Medical,Medicare,Medicaid,,,true,11799,2595.72
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,105,F,Other Race,Not Span/Hispanic,3,Emergency,Psychiatric Hospital or Unit of Hosp,2019,MBD012,SUICIDAL IDEATION/ATTEMPT/INTENTIONAL SELF-HARM,MST007,HIP ARTHROPLASTY,817,OVERDOSE,21,"INJURIES, POISONINGS AND TOXIC EFFECTS OF DRUGS",2,Moderate,Moderate,Medical,Medicare,Private Health Insurance,,,true,52711,11355.62
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,50 to 69,105,M,Other Race,Not Span/Hispanic,5,Emergency,Home or Self Care,2019,END003,DIABETES MELLITUS WITH COMPLICATION,MST020,SUBCUTANEOUS TISSUE AND FASCIA EXCISION,344,"OSTEOMYELITIS, SEPTIC ARTHRITIS & OTHER MUSCULOSKELETAL INFECTIONS",08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Moderate,Medical,Private Health Insurance,,,,true,51161,12017.19
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,OOS,F,Other Race,Not Span/Hispanic,9,Emergency,Home or Self Care,2019,DIG012,INTESTINAL OBSTRUCTION AND ILEUS,GIS022,GI SYSTEM LYSIS OF ADHESIONS,223,OTHER SMALL & LARGE BOWEL PROCEDURES,06,DISEASES AND DISORDERS OF THE DIGESTIVE SYSTEM,3,Major,Major,Surgical,Medicare,,,,true,96700,18905.83
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,50 to 69,105,F,Other Race,Not Span/Hispanic,3,Emergency,Home or Self Care,2019,RSP002,PNEUMONIA (EXCEPT THAT CAUSED BY TUBERCULOSIS),,,139,OTHER PNEUMONIA,04,DISEASES AND DISORDERS OF THE RESPIRATORY SYSTEM,3,Major,Major,Medical,Medicare,Medicaid,,,true,28909,6740.04
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,50 to 69,106,M,Black/African American,Not Span/Hispanic,1,Emergency,Home or Self Care,2019,CIR012,NONSPECIFIC CHEST PAIN,,,203,CHEST PAIN,05,DISEASES AND DISORDERS OF THE CIRCULATORY SYSTEM,2,Moderate,Minor,Medical,Medicare,,,,true,26740,5209.08
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,105,F,Other Race,Not Span/Hispanic,4,Emergency,Home w/ Home Health Services,2019,SKN001,SKIN AND SUBCUTANEOUS TISSUE INFECTIONS,,,383,CELLULITIS & OTHER SKIN INFECTIONS,09,"DISEASES AND DISORDERS OF THE SKIN, SUBCUTANEOUS TISSUE AND BREAST",2,Moderate,Moderate,Medical,Medicare,Private Health Insurance,,,true,32346,6999.27
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,106,M,Other Race,Not Span/Hispanic,2,Emergency,Home or Self Care,2019,SYM002,FEVER,,,722,FEVER,18,INFECTIOUS AND PARASITIC DISEASES (SYSTEMIC OR UNSPECIFIED SITES),2,Moderate,Minor,Medical,Medicare,Blue Cross/Blue Shield,,,true,16346,4300.71
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,106,F,Other Race,Not Span/Hispanic,1,Emergency,Home or Self Care,2019,DIG012,INTESTINAL OBSTRUCTION AND ILEUS,,,247,INTESTINAL OBSTRUCTION,06,DISEASES AND DISORDERS OF THE DIGESTIVE SYSTEM,3,Major,Major,Medical,Medicare,,,,true,11282,2284.73
Hudson Valley,Westchester,5902001,001045,White Plains Hospital Center,70 or Older,106,M,Other Race,Not Span/Hispanic,4,Emergency,Home or Self Care,2019,RSP002,PNEUMONIA (EXCEPT THAT CAUSED BY TUBERCULOSIS),,,139,OTHER PNEUMONIA,04,DISEASES AND DISORDERS OF THE RESPIRATORY SYSTEM,3,Major,Moderate,Medical,Medicare,Private Health Insurance,,,true,38689,9001.06
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,30 to 49,OOS,M,White,Not Span/Hispanic,1,Elective,Home w/ Home Health Services,2019,MUS006,OSTEOARTHRITIS,MST007,HIP ARTHROPLASTY,301,HIP JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Minor,Surgical,Private Health Insurance,,,,false,171695.19,27119.19
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,50 to 69,OOS,M,White,Not Span/Hispanic,4,Elective,Home w/ Home Health Services,2019,MUS006,OSTEOARTHRITIS,MST007,HIP ARTHROPLASTY,301,HIP JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Minor,Surgical,Private Health Insurance,,,,false,215167.53,40692.52
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,30 to 49,103,M,White,Not Span/Hispanic,7,Elective,Home w/ Home Health Services,2019,INJ037,"COMPLICATION OF OTHER SURGICAL OR MEDICAL CARE, INJURY, INITIAL ENCOUNTER",MST004,INCISION AND DRAINAGE OF MUSCULOSKELETAL TISSUE AND JOINTS,711,"POST-OP, POST-TRAUMA, OTHER DEVICE INFECTIONS W O.R. PROCEDURE",18,INFECTIOUS AND PARASITIC DISEASES (SYSTEMIC OR UNSPECIFIED SITES),3,Major,Major,Surgical,Miscellaneous/Other,Blue Cross/Blue Shield,,,false,104796.21,37762.06
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,30 to 49,112,M,Other Race,Not Span/Hispanic,1,Elective,Home or Self Care,2019,MUS011,SPONDYLOPATHIES/SPONDYLOARTHROPATHY (INCLUDING INFECTIVE),MST013,SPINE FUSION,321,CERVICAL SPINAL FUSION & OTHER BACK/NECK PROC EXC DISC EXCIS/DECOMP,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Minor,Surgical,Private Health Insurance,,,,false,194117.25,31406.84
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,50 to 69,104,F,Black/African American,Not Span/Hispanic,3,Elective,Skilled Nursing Home,2019,INJ035,"COMPLICATION OF INTERNAL ORTHOPEDIC DEVICE OR IMPLANT, INITIAL ENCOUNTER",MST006,KNEE ARTHROPLASTY,302,KNEE JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Minor,Surgical,Blue Cross/Blue Shield,,,,false,257171.1,44434.58
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,70 or Older,112,F,Other Race,Not Span/Hispanic,3,Elective,Skilled Nursing Home,2019,MUS006,OSTEOARTHRITIS,MST006,KNEE ARTHROPLASTY,302,KNEE JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,1,Minor,Minor,Surgical,Medicare,,,,false,104628.37,21668.1
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,50 to 69,113,F,White,Not Span/Hispanic,3,Elective,Home w/ Home Health Services,2019,MUS006,OSTEOARTHRITIS,MST007,HIP ARTHROPLASTY,301,HIP JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,3,Major,Minor,Surgical,Medicaid,,,,false,206263.82,36636.28
New York City,Manhattan,7002053,001446,NYU Langone Orthopedic Hospital,30 to 49,104,F,White,Not Span/Hispanic,2,Elective,Home w/ Home Health Services,2019,MUS006,OSTEOARTHRITIS,MST007,HIP ARTHROPLASTY,301,HIP JOINT REPLACEMENT,08,DISEASES AND DISORDERS OF THE MUSCULOSKELETAL SYSTEM AND CONNECTIVE TISSUE,2,Moderate,Minor,Surgical,Blue Cross/Blue Shield,Blue Cross/Blue Shield,,,false,190443.58,31916.43
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,0 to 17,104,F,Other Race,Spanish/Hispanic,37,Emergency,Home or Self Care,2019,MBD001,SCHIZOPHRENIA SPECTRUM AND OTHER PSYCHOTIC DISORDERS,MHT001,PHARMACOTHERAPY FOR MENTAL HEALTH (EXCLUDING SUBSTANCE USE),751,MAJOR DEPRESSIVE DISORDERS & OTHER/UNSPECIFIED PSYCHOSES,19,MENTAL DISEASES AND DISORDERS,1,Minor,Minor,Medical,Self-Pay,,,,false,56670.83,37663.43
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,50 to 69,104,M,Black/African American,Not Span/Hispanic,48,Emergency,Home or Self Care,2019,MBD001,SCHIZOPHRENIA SPECTRUM AND OTHER PSYCHOTIC DISORDERS,MHT001,PHARMACOTHERAPY FOR MENTAL HEALTH (EXCLUDING SUBSTANCE USE),750,SCHIZOPHRENIA,19,MENTAL DISEASES AND DISORDERS,2,Moderate,Minor,Medical,Medicaid,,,,true,68635.76,45615.33
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,50 to 69,100,M,Other Race,Spanish/Hispanic,5,Emergency,Home or Self Care,2019,MBD017,ALCOHOL-RELATED DISORDERS,SUD001,SUBSTANCE USE DETOXIFICATION,772,ALCOHOL & DRUG DEPENDENCE W REHAB OR REHAB/DETOX THERAPY,20,ALCOHOL/DRUG USE AND ALCOHOL/DRUG INDUCED ORGANIC MENTAL DISORDERS,2,Moderate,Moderate,Medical,Medicaid,,,,true,8008.06,5322.16
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,50 to 69,100,M,Other Race,Not Span/Hispanic,6,Emergency,Home or Self Care,2019,BLD006,COAGULATION AND HEMORRHAGIC DISORDERS,SUD001,SUBSTANCE USE DETOXIFICATION,661,COAGULATION & PLATELET DISORDERS,16,DISEASES AND DISORDERS OF THE BLOOD AND BLOOD FORMING ORGANS AND IMMUNOLOGICAL DISORDERS,1,Minor,Moderate,Medical,Medicaid,,,,true,38721.94,25734.6
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,30 to 49,112,M,Other Race,Spanish/Hispanic,2,Emergency,Home or Self Care,2019,MBD017,ALCOHOL-RELATED DISORDERS,SUD001,SUBSTANCE USE DETOXIFICATION,772,ALCOHOL & DRUG DEPENDENCE W REHAB OR REHAB/DETOX THERAPY,20,ALCOHOL/DRUG USE AND ALCOHOL/DRUG INDUCED ORGANIC MENTAL DISORDERS,2,Moderate,Minor,Medical,Medicaid,,,,true,4022.74,2673.51
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,50 to 69,112,F,Black/African American,Not Span/Hispanic,1,Emergency,Home or Self Care,2019,CIR008,HYPERTENSION WITH COMPLICATIONS AND SECONDARY HYPERTENSION,,,199,HYPERTENSION,05,DISEASES AND DISORDERS OF THE CIRCULATORY SYSTEM,2,Moderate,Moderate,Medical,Medicaid,,,,true,11535.34,7666.39
New York City,Manhattan,7002001,001438,Bellevue Hospital Center,30 to 49,100,M,Black/African American,Not Span/Hispanic,39,Emergency,Inpatient Rehabilitation Facility,2019,INJ021,"EFFECT OF OTHER EXTERNAL CAUSES, INITIAL ENCOUNTER",RES007,AIRWAY INTUBATION,792,EXTENSIVE OR PROCEDURES FOR OTHER COMPLICATIONS OF TREATMENT,21,"INJURIES, POISONINGS AND TOXIC EFFECTS OF DRUGS",4,Extreme,Extreme,Surgical,Medicaid,Medicaid,,,true,224350.76,149103.52

方案 1

创建表脚本（浮点数）：

DROP TABLE IF EXISTS NYHospitals.dbo.SPARC2019
GO
CREATE TABLE NYhospitals.dbo.SPARC2019
    (
        ID INT IDENTITY (1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY, 
        Hospital_Service_Area VARCHAR(15) null,
        Hospital_County VARCHAR(11) null,
        Operating_Certificate_Number VARCHAR(12) null,
        Permanent_Facility_Id INT null,
        Facility_Name VARCHAR(112) null,
        Age_Group VARCHAR(11) null,
        Zip_Code_3_digits VARCHAR(3) null,
        Gender VARCHAR(1) null,
        Race VARCHAR(32) null,
        Ethnicity VARCHAR(20) null,
        Length_of_Stay VARCHAR(5) null,
        Type_of_Admission VARCHAR(15) null,
        Patient_Disposition VARCHAR(37) null,
        Discharge_Year VARCHAR(4) null,
        CCSR_Diagnosis_Code VARCHAR(10) null,
        CCSR_Diagnosis_Description VARCHAR(250) null,
        CCSR_Procedure_Code VARCHAR(10) null,
        CCSR_Procedure_Description VARCHAR(250) null,
        APR_DRG_Code VARCHAR(3) null,
        APR_DRG_Description VARCHAR(500) null,
        APR_MDC_Code VARCHAR(2) null,
        APR_MDC_Description VARCHAR(500) null,
        APR_Severity_of_Illness_Code VARCHAR(1) null,
        APR_Severity_of_Illness_Description VARCHAR(8) null,
        APR_Risk_of_Mortality VARCHAR(8) null,
        APR_Medical_Surgical_Description VARCHAR(14) null,
        Payment_Typology_1 VARCHAR(25) null,
        Payment_Typology_2 VARCHAR(25) null,
        Payment_Typology_3 VARCHAR(25) null,
        Birth_Weight VARCHAR(5) null,
        Emergency_Department_Indicator VARCHAR(12) null,
        Total_Charges float null,
        Total_Costs float null

    )

创建视图脚本（允许批量插入以将数据导入到表中）

--Allows data to be passed through it using bulkinsert from flatfile source whose column count does not match destination table SPARC2019.
DROP VIEW IF EXISTS dbo.rawdataview
GO
CREATE VIEW rawdataview
AS 
    SELECT 

        Hospital_Service_Area,
        Hospital_County,
        Operating_Certificate_Number,
        Permanent_Facility_Id,
        Facility_Name,
        Age_Group,
        Zip_Code_3_digits,
        Gender,
        Race,
        Ethnicity,
        Length_of_Stay,
        Type_of_Admission,
        Patient_Disposition,
        Discharge_Year,
        CCSR_Diagnosis_Code,
        CCSR_Diagnosis_Description,
        CCSR_Procedure_Code,
        CCSR_Procedure_Description,
        APR_DRG_Code,
        APR_DRG_Description,
        APR_MDC_Code,
        APR_MDC_Description,
        APR_Severity_of_Illness_Code,
        APR_Severity_of_Illness_Description,
        APR_Risk_of_Mortality,
        APR_Medical_Surgical_Description,
        Payment_Typology_1,
        Payment_Typology_2,
        Payment_Typology_3,
        Birth_Weight,
        Emergency_Department_Indicator,
        Total_Charges,
        Total_Costs
    
    FROM dbo.sparc2019

批量插入脚本

USE NYHospitals

BULK INSERT NYHospitals.dbo.rawdataview
FROM 'C:\Users\Bob\Downloads\Hospital_Inpatient_Discharges__SPARCS_De-Identified___2019.csv'
    WITH
        (
        BATCHSIZE = 100000
        ,ERRORFILE = 'C:\Users\Bob\Downloads\Hospital_Inpatient_Discharges__SPARCS_De-Identified___2019_ERROR.log'
        ,MAXERRORS = 1
        ,FIRSTROW = 2
        ,KEEPNULLS
        ,FORMAT = 'CSV'
        ,FIELDTERMINATOR = ','
        ,ROWTERMINATOR = '0x0a'
        )

运行大容量插入脚本（float 数据类型）后的输出

预期结果是 ex. 190443.58

方案 2

创建表脚本（十进制）

DROP TABLE IF EXISTS NYHospitals.dbo.SPARC2019
GO
CREATE TABLE NYhospitals.dbo.SPARC2019
    (
        ID INT IDENTITY (1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY, 
        Hospital_Service_Area VARCHAR(15) null,
        Hospital_County VARCHAR(11) null,
        Operating_Certificate_Number VARCHAR(12) null,
        Permanent_Facility_Id INT null,
        Facility_Name VARCHAR(112) null,
        Age_Group VARCHAR(11) null,
        Zip_Code_3_digits VARCHAR(3) null,
        Gender VARCHAR(1) null,
        Race VARCHAR(32) null,
        Ethnicity VARCHAR(20) null,
        Length_of_Stay VARCHAR(5) null,
        Type_of_Admission VARCHAR(15) null,
        Patient_Disposition VARCHAR(37) null,
        Discharge_Year VARCHAR(4) null,
        CCSR_Diagnosis_Code VARCHAR(10) null,
        CCSR_Diagnosis_Description VARCHAR(250) null,
        CCSR_Procedure_Code VARCHAR(10) null,
        CCSR_Procedure_Description VARCHAR(250) null,
        APR_DRG_Code VARCHAR(3) null,
        APR_DRG_Description VARCHAR(500) null,
        APR_MDC_Code VARCHAR(2) null,
        APR_MDC_Description VARCHAR(500) null,
        APR_Severity_of_Illness_Code VARCHAR(1) null,
        APR_Severity_of_Illness_Description VARCHAR(8) null,
        APR_Risk_of_Mortality VARCHAR(8) null,
        APR_Medical_Surgical_Description VARCHAR(14) null,
        Payment_Typology_1 VARCHAR(25) null,
        Payment_Typology_2 VARCHAR(25) null,
        Payment_Typology_3 VARCHAR(25) null,
        Birth_Weight VARCHAR(5) null,
        Emergency_Department_Indicator VARCHAR(12) null,
        Total_Charges decimal(12,2) null,
        Total_Costs decimal(12,2) null

    )

运行批量插入脚本（十进制数据类型）后的输出

预期结果是 ex. 190443.58