CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
S. S. Rawat
S. S. Rawat

Reputation: 6131

Bulk insert with primary key Id

I want to bulk insert file data into the table with primary key id column but I get this error.

CREATE TABLE [dbo].[WorkDataLoad]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RecordLine] [nvarchar](max) NULL,

    CONSTRAINT [PK_WorkDataLoad] PRIMARY KEY CLUSTERED ([Id] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Query:

truncate table WorkDataLoad

BULK INSERT WorkDataLoad
   FROM 'D:\NPfiles\TS082114.trn'
   WITH (
        TABLOCK,
        KEEPIDENTITY
   )

Error

Msg 4832, Level 16, State 1, Line 4
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 4
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Upvotes: 2

Views: 4296

Answers (1)

Deep Sharma
Deep Sharma

Reputation: 3483

Keep your table as it is and create this VIEW (select everything except the ID(Primary Key) column)

CREATE VIEW [dbo].[VWWorkDataLoad]
AS
SELECT RecordLine
FROM [dbo].[WorkDataLoad];

Now BULK INSERT should then look like:

BULK INSERT [dbo].[VWWorkDataLoad] FROM 'D:\NPfiles\TS082114.trn' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

After running this statement you can check your table it should be having records with primary key. Hope this helps, it worked for me.

Upvotes: 4

Related Questions