Megosztás a következőn keresztül:

Null értékek vagy alapértelmezett értékek megőrzése a tömeges importálás során (SQL Server)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Ha adatokat importál egy táblába, a bcp parancs és BULK INSERT utasítás a tábla oszlopaihoz definiált alapértelmezett értékeket figyeli meg. Ha például egy adatfájlban null mező van, az oszlop alapértelmezett értéke lesz betöltve. A bcp parancs és BULK INSERT utasítás egyaránt lehetővé teszi a null értékek megőrzését.

Ezzel szemben egy reguláris INSERT utasítás megtartja a null értéket az alapértelmezett érték beszúrása helyett. Az INSERT ... SELECT * FROM OPENROWSET BULK utasítás ugyanazt az alapvető viselkedést biztosítja, mint a normál INSERT, de támogatja a táblázat tipp beszúrásához az alapértelmezett értékeket.

Null értékek megőrzése

A következő minősítők azt adják meg, hogy az adatfájl üres mezője megtartja null értékét a tömeges importálási művelet során, ahelyett, hogy örökölné a táblaoszlopok alapértelmezett értékét (ha van ilyen). Az OPENROWSET BULK esetében alapértelmezés szerint a tömeges terheléses műveletben nem megadott oszlopok a következőre NULLvannak állítva: .

Parancs Kiegészítő Minősítő típusa
bcp -k Kapcsoló
BULK INSERT KEEPNULLS* Érvelés
INSERT ... SELECT * FROM OPENROWSET(BULK...) Nincs adat. Nincs adat.

* A BULK INSERT (Transact-SQL) esetében, ha az alapértelmezett értékek nem érhetők el, a tábla oszlopát meg kell határozni a null értékek engedélyezéséhez.

Jegyzet

Ezek a minősítők letiltják a DEFAULT definíciók ellenőrzését a táblákon ezen tömeges importálási parancsokkal. Az egyidejű INSERT utasítások DEFAULT esetében azonban definíciók várhatók.

Alapértelmezett értékek használata az INSERT ... SELECT * FROM OPENROWSET BULK

Megadhatja, hogy az adatfájl üres mezőjéhez a megfelelő táblaoszlop az alapértelmezett értéket használja (ha van ilyen). Az alapértelmezett értékek használatához használjon táblázatos tippeket.

További információ: OPENROWSET BULK.

Példa tesztelési feltételekre

A példák a cikkben létrehozott adatbázist és fájlformátumot használják.

Módosítsa a kódminta helyi fájlhelyét úgy, hogy az a számítógépén lévő fájlhelyre mutasson.

Mintatábla

A szkript létrehoz egy tesztadatbázist és egy táblát.myNulls A negyedik táblaoszlop Kidsalapértelmezett értékkel rendelkezik. Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

Mintaadatfájl

A Jegyzettömb használatával hozzon létre egy üres fájlt D:\BCP\myNulls.bcp , és szúrja be a következő mintaadatokat. A harmadik rekordban, a negyedik oszlopban nincs érték.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

Másik lehetőségként a következő PowerShell-szkriptet is végrehajthatja az adatfájl létrehozásához és feltöltéséhez:

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'MyNulls.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Nem XML formátumú minta fájl

Az SQL Server kétféle formátumfájlt támogat: nem XML- és XML-formátumot. A nem XML formátum az SQL Server korábbi verziói által támogatott eredeti formátum. További információ: Nem XML formátumú fájlok használata (SQL Server).

Az alábbi parancs a bcp segédprogrammal hoz létre egy nem XML formátumú fájlt a myNulls.fmtséma myNullsalapján.

  • Ha bcp parancsot szeretne használni egy formátumfájl létrehozásához, adja meg a format argumentumot, és adatfájl elérési útja helyett használja a nul.
  • A formátumbeállításhoz szükség van a -f beállításra is.
  • c karakteradatok megadására szolgál
  • t,vesszőt mezőelválasztóként való megadására szolgál.
  • T megbízható kapcsolat megadására szolgál az integrált biztonság használatával.

A parancssorban adja meg a következő parancsot:

bcp TestDatabase.dbo.myNulls format nul -c -f D:\BCP\myNulls.fmt -t, -T

REM Review file
Notepad D:\BCP\myNulls.fmt

Fontos

Győződjön meg arról, hogy a nem XML formátumú fájl egy kocsivissza\sor hírcsatornával végződik. Ellenkező esetben valószínűleg a következő hibaüzenet jelenik meg:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

További információ a formátumfájlok létrehozásáról: Formátumfájl létrehozása bcp-vel (SQL Server).

Null értékek megőrzése vagy alapértelmezett értékek használata tömeges importáláskor

A példák a cikkben létrehozott adatbázist, adatfájlt és formátumfájlokat használják.

A bcp használata és a null értékek megőrzése formátumfájl nélkül

A -k kapcsoló.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

A bcp használata és a null értékek megőrzése nem XML formátumú fájllal

A kapcsolók -k és -f.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T -k

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Bcp és alapértelmezett értékek használata formátumfájl nélkül

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -c -t, -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

Bcp és alapértelmezett értékek használata nem XML formátumú fájllal

A -f kapcsoló.

A parancssorban adja meg a következő parancsot:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myNulls;"

REM Import data
bcp TestDatabase.dbo.myNulls IN D:\BCP\myNulls.bcp -f D:\BCP\myNulls.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myNulls;"

A BULK INSERT használata és a null értékek megőrzése formátumfájl nélkül

Az KEEPNULLS argumentum.

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO
TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
    FROM 'D:\BCP\myNulls.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

A BULK INSERT használata és a null értékek megőrzése nem XML formátumú fájllal

Az KEEPNULLS és az FORMATFILE argumentum.

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls; -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt',
        KEEPNULLS
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

A BULK INSERT használata és az alapértelmezett értékek használata formátumfájl nélkül

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

TÖMEGES BESZÚRÁS és alapértelmezett értékek használata nem XML formátumú fájllal

Az FORMATFILE argumentum.

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
BULK INSERT dbo.myNulls
   FROM 'D:\BCP\myNulls.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myNulls.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

AZ OPENROWSET BULK használata és a null értékek megőrzése nem XML formátumú fájllal

Az FORMATFILE argumentum.

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

AZ OPENROWSET BULK használata, és az alapértelmezett értékek megőrzése nem XML formátumú fájllal

A KEEPDEFAULTS táblázat tippje és FORMATFILE argumentuma.

Hajtsa végre a következő Transact-SQL a Microsoft SQL Server Management Studióban (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myNulls;

Formátumfájl használata

Adatformátumok használata tömeges importáláshoz vagy tömeges exportáláshoz

Adatformátumok megadása a kompatibilitás érdekében, amikor a bcp-t használja

Kapcsolódó tartalom

  • Last updated on