SQL Server Bulk Insert with Primary Key Id-CodePudding

I want to bulk insert a csv file into this table. My csv does not have the incremental pk field, so I created a view of this table without the pk column and tried to bulk insert in the view.

I still get an error

Cannot insert the value NULL into column 'CNPJ_ID', table 'CVM.dbo.Hist'; column does not allow nulls. INSERT fails.

Any suggestions?

CREATE TABLE [dbo].[Hist]
(
    [CNPJ_ID] [INT] IDENTITY(1,1) PRIMARY KEY,
    [TP_FUNDO] [text] NULL,
    [CNPJ_FUNDO] [nvarchar](max) NULL,
    [DT_COMPTC] [varchar](50) NOT NULL,
    [VL_TOTAL] [varchar](50) NOT NULL,
    [VL_QUOTA] [varchar](50) NOT NULL,
    [VL_PATRIM_LIQ] [varchar](50) NOT NULL,
    [CAPTC_DIA] [varchar](50) NOT NULL,
    [RESG_DIA] [varchar](50) NOT NULL,
    [NR_COTST] [int] NULL   
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE VIEW [VWHist] AS
    SELECT 
        [TP_FUNDO],
        [CNPJ_FUNDO],
        [DT_COMPTC],
        [VL_TOTAL],
        [VL_QUOTA],
        [VL_PATRIM_LIQ],
        [CAPTC_DIA],
        [RESG_DIA],
        [NR_COTST]
    FROM 
        Hist;

BULK INSERT [dbo].[VWHist]
FROM 'C:\anaconda3\Docs\2022.csv'
    WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '0x0a',
    FIRSTROW =2,
    ROWS_PER_BATCH = 100000
    );

CodePudding user response：

Use OPENROWSET(BULK . . .) instead and you can write a normal INSERT ... SELECT.

CodePudding user response：

It's been a few years, but IIRC you want bcp -E. Unless you use a format file, your source file must have the same number of columns as the table you're loading. The -E option causes SQL Server to ignore the value in the file for the Identity column, and substitute its own instead.