Hi,
I Have an txt file as below :
date trans_id
4/21/2015 0:00,ANI.017.SC14111019.2D
4/20/2015 0:00,ANI.059.DC15011102.4B
4/20/2015 0:00,ANI.065.DC15011104.1B
4/21/2015 0:00,ANN.004.SL15022404.2B
4/21/2015 0:00,ANN.005.DC15040401.1B
from the data I want to import into my table with same field name (date, trans_id).
I try import the data with the task menu, import data and it is work.
Next way I want to import it by sql syntax such as :
SET IDENTITY_INSERT bcode ON
BULK INSERT bcode FROM 'd:\bcode\barcodewhr2.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR ='\r\n') ;
SET IDENTITY_INSERT bcode OFF
but there's an error said " Table 'bcode' does not have the identity property. Cannot perform SET operation."
the table destination don't have a primary key it just temporary table.
Please help..
Thx
If your target able does not have a column with the IDENTITY property then you don't need (and cannot use) SET IDENTITY_INSERT.
Either add an IDENTITY column to [bcode] (if you need one), or just leave out both the SET IDENTITY_INSERT commands
if the identity set were remove there is an error message :
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
so what is teh best solution to import txt file with sql syntax?
thx
if the columns (both the number of columns, and the ordering of them) do not EXACTLY match your table then you need to use a FORMATFILE (i.e. to list the columns, in the order that they appear in your Import File)
You might be able to make a VIEW which matches the column order, and import into the View instead of the Table, if that is easier (although a FORMATFILE lets you do other things, e.g. specifying the Collation)
I am assuming that this is because the number of columns in the import file is not the same as the table, but it might be that there is an extra line-break at the end of the file, or there is no line break there (although I don't think that matters)