It is necessary to add data to the table from the file with the csv extension.

Data file format: Название;Номер1;Номер2;houseId;html_ссылка
Table format: houseid | html-ссылка | houseid | html-ссылка |
houseid in the table is the primary and foreign key.
In the query, you need to skip the three columns from the data file and add the last two to the table.

With bcp, I tried to make a format file:

 <?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="\r\n" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="21"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="4" NAME="HouseId" xsi:type="SQLBIGINT"/> <COLUMN SOURCE="5" NAME="Link" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT>

Wrote a request:

  BULK INSERT dbo.Aeropanoramas FROM 'c:\1\nmarket_aero.csv' WITH ( ERRORFILE = 'c:\1\error_file.xml', FORMATFILE = 'c:\1\sql_format_file', FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', TABLOCK )

When I execute the request, I get the error:

 Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)"

    1 answer 1

    According to the documentation , the format file should be

     <?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="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="21"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Cyrillic_General_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="4" NAME="HouseId" xsi:type="SQLBIGINT"/> <COLUMN SOURCE="5" NAME="Link" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT>

    And the terminators in the request are not needed:

    The information in the <ROW> element allows you to build a string without additional information. For this reason, the rowset can be formed using the instructions

    SELECT (SELECT * FROM OPENROWSET(BULK datafile FORMATFILE=xmlformatfile)