1

Topic: BULK INSERT (OPENROWSET) how to interpose NULL?

Good afternoon, I want at import of a file with separators "\t", a line end "\n" at an empty field in a file into the table to interpose null how it to implement?
Example of a script for an insertion

INSERT INTO Biovia_Prices
SELECT *
FROM OPENROWSET (BULK ' \\server\public\outputMain.txt ',
FORMATFILE = ' \\server\public\id_skip.xml'
) AS t1;

FORMATFILE

<? 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 = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "2" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "3" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "4" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "5" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "6" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "7" xsi:type = "CharTerm" TERMINATOR = "\t" MAX_LENGTH = "100"/>
<FIELD ID = "8" xsi:type = "CharTerm" TERMINATOR = "\n" MAX_LENGTH = "100"/>
</RECORD>
<ROW>
<COLUMN SOURCE = "1" NAME = "mdlnumber" xsi:type = "SQLNVARCHAR"/>
<COLUMN SOURCE = "2" NAME = "sub_code" xsi:type = "SQLNVARCHAR"/>
<COLUMN SOURCE = "3" NAME = "supplier_name" xsi:type = "SQLNVARCHAR"/>
<COLUMN SOURCE = "4" NAME = "weight_val" xsi:type = "SQLFLT8"/>
<COLUMN SOURCE = "5" NAME = "weight_type" xsi:type = "SQLNVARCHAR"/>
<COLUMN SOURCE = "6" NAME = "price" xsi:type = "SQLFLT8"/>
<COLUMN SOURCE = "7" NAME = "currency" xsi:type = "SQLNVARCHAR"/>
<COLUMN SOURCE = "8" NAME = "purity" xsi:type = "SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Imported file
Approximately so

wrote:

MFCD00000001 AB118546 ABCR 1 G 123.45 EUR 90%
MFCD00000001 AB118546 ABCR 10 G 123.00 EUR

DDL tables

CREATE TABLE [dbo]. [Biovia_Prices] (
[id] int IDENTITY (1, 1) NOT NULL;
[mdlnumber] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NOT NULL;
[sub_code] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NOT NULL;
[supplier_name] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NOT NULL;
[weight_val] float NOT NULL;
[weight_type] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NOT NULL;
[price] float NOT NULL;
[currency] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NOT NULL;
[purity] varchar (50) COLLATE SQL_Ukrainian_CP1251_CI_AS NULL;
CONSTRAINT [PK_biovia_prices] PRIMARY KEY CLUSTERED ([id])
)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Biovia_Prices_idx] ON [dbo]. [Biovia_Prices]
([sub_code])
WITH (
PAD_INDEX = OFF;
DROP_EXISTING = OFF;
STATISTICS_NORECOMPUTE = OFF;
SORT_IN_TEMPDB = OFF;
ONLINE = OFF;
ALLOW_ROW_LOCKS = ON;
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Biovia_Prices_idx2] ON [dbo]. [Biovia_Prices]
([supplier_name])
WITH (
PAD_INDEX = OFF;
DROP_EXISTING = OFF;
STATISTICS_NORECOMPUTE = OFF;
SORT_IN_TEMPDB = OFF;
ONLINE = OFF;
ALLOW_ROW_LOCKS = ON;
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Biovia_Prices_idx3] ON [dbo]. [Biovia_Prices]
([currency])
WITH (
PAD_INDEX = OFF;
DROP_EXISTING = OFF;
STATISTICS_NORECOMPUTE = OFF;
SORT_IN_TEMPDB = OFF;
ONLINE = OFF;
ALLOW_ROW_LOCKS = ON;
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Biovia_Prices_uq] ON [dbo]. [Biovia_Prices]
([mdlnumber])
WITH (
PAD_INDEX = OFF;
DROP_EXISTING = OFF;
STATISTICS_NORECOMPUTE = OFF;
SORT_IN_TEMPDB = OFF;
ONLINE = OFF;
ALLOW_ROW_LOCKS = ON;
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

2

Re: BULK INSERT (OPENROWSET) how to interpose NULL?

wissem;
And what that is not interposed?

3

Re: BULK INSERT (OPENROWSET) how to interpose NULL?

The last field in a file purity. Instead of empty value into the table it should be interposed null.

4

Re: BULK INSERT (OPENROWSET) how to interpose NULL?

wissem wrote:

the last field in a file purity. Instead of empty value into the table it should be interposed null.

And how BULK should guess your desire?

5

Re: BULK INSERT (OPENROWSET) how to interpose NULL?

https://docs.microsoft.com/ru-ru/sql/re … sql-server - section "Saving of values NULL"

6

Re: BULK INSERT (OPENROWSET) how to interpose NULL?

How at that request which I wrote in a subject, through openrowset to make that? There keepnulls directive does not work. For this purpose to launch BCP the utility from command line at me there is no access to the server. Already made with the help not xml formatfile by an example of a file which is generated BCP, but without BCP.