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
No comments:
Post a Comment