Table created in SQL Server

create table tbl (Id int, [Date] date, Notes nvarchar(max))

To load data into the tbl table created tbl.fmt

 12.0 3 1 SQLINT 0 4 "," 1 Id "" 2 SQLDATETIME 0 8 "," 2 Date "" 3 SQLCHAR 0 0 "\r\n" 3 Notes ""

Following code

 bulk insert tbl from 'd:\tbl.txt' with ( firstrow = 2, DATAFILETYPE = 'widechar', FORMATFILE = 'd\tbl.fmt', tablock )

for the file d: \ tbl.txt

 Id, Date, Notes "1", "2016-01-01", "какой-то текст"

gives an error message:

Implicit conversion from data type Use the CONVERT function to run this query.

How to make the correct loading using FORMATFILE?

  • prefix 1 set and the length of the values ​​change for date 10, and the separator "". specify the -t parameter for bcp (this is a comma as a separator). 3 column - set the length - Konst
  • 8 replaced by 10, but I don’t understand what to do about the prefix? - Box

1 answer 1

test.txt (prepare the correct data file)

 Id,Date,Note 1,2016-01-01,какой-то текст

test.fmt (for it the corresponding formatting file)

 10.0 3 1 SQLCHAR 0 4 "," 1 Id "" 2 SQLCHAR 0 10 "," 2 Date "" 3 SQLCHAR 0 100 "\r\n" 3 Notes SQL_Latin1_General_CP1251_CI_AS

check:

 SELECT * FROM OPENROWSET ( BULK 'd:\MSSQL\test.txt', FORMATFILE='d:\MSSQL\test.fmt', FIRSTROW=2 ) as t;

and only then insert with the conversion fields in the desired table