An explicit column list must be specified for target table '' when table hint KEEPIDENTITY is used and the table contains an identity column.

Suppose we have text file data.txt which has following data delimited by comma:

1,Scott

2,Greg

3,Raja

We want to import those data into the table EmpList:

CREATE TABLE EmpList(

    ID INT IDENTITY,

    Name VARCHAR(500)

)

For this we have created the xml format file foramt.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="100" />

    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

  </RECORD>

  <ROW>

    <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLSMALLINT"/>

    <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>

  </ROW>

</BCPFORMAT>

If we execute following to bulk insert:

INSERT INTO EmpList WITH(KEEPIDENTITY)

SELECT *

FROM  OPENROWSET(BULK 'd:\data.txt',FORMATFILE = 'd:\format.xml') AS tbl

We will get error message :

An explicit column list must be specified for target table '' when table hint KEEPIDENTITY is used and the table contains an identity column.

Cause: If we are using table hint KEEPIDENTITY then in this case it is necessary to specify column name.

Solution:

INSERT INTO EmpList WITH(KEEPIDENTITY)(Name)

SELECT Name

FROM  OPENROWSET(BULK 'd:\data.txt',FORMATFILE = 'd:\format.xml') AS tbl