Resolved: SQL Server Bulk Insert with Primary Key Id

Question:

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
    );

Answer:

We don’t have your source file: ‘C:\anaconda3\Docs\2022.csv’. So no minimal reproducible example was provided.
The official documentation [KEEPIDENTITY][1] clearly states how to handle your scenario:

If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view is to be skipped when importing data; SQL Server automatically assigns unique values for the column.

Here is a conceptual example for you.
Source file: myIdentity_No_PK.csv

Anthony,Grosse,1980-02-23
Alica,Fatnowna,1963-11-14
Stella,Rosenhain,1992-03-02
Miller,Dylan,1954-01-05

XML Format file: myIdentity_No_PK.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <RECORD>
      <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="70"/>
      <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="70"/>
      <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="10"/>
   </RECORD>
   <ROW>
      <COLUMN SOURCE="1" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="3" NAME="BirthDate" xsi:type="SQLDATE"/>
   </ROW>
</BCPFORMAT>

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.myIdentity;

CREATE TABLE dbo.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
);

INSERT INTO dbo.myIdentity (FirstName, LastName, BirthDate)
SELECT * FROM  OPENROWSET(
BULK 'E:\Temp\myIdentity_No_PK.csv',
FORMATFILE='e:\Temp\myIdentity_No_PK.xml') as t;


-- review results
SELECT * FROM dbo.myIdentity;

Output

+----------+-----------+-----------+------------+
| PersonID | FirstName | LastName  | BirthDate  |
+----------+-----------+-----------+------------+
|        1 | Anthony   | Grosse    | 1980-02-23 |
|        2 | Alica     | Fatnowna  | 1963-11-14 |
|        3 | Stella    | Rosenhain | 1992-03-02 |
|        4 | Miller    | Dylan     | 1954-01-05 |
+----------+-----------+-----------+------------+

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Question:

Answer:

Related