Behåll nullvärden eller standardvärden under massimport (SQL Server)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

När data importeras till en tabell observerar kommandot bcp och BULK INSERT -instruktionen alla standardvärden som har definierats för kolumnerna i tabellen. Om det till exempel finns ett null-fält i en datafil läses standardvärdet för kolumnen in i stället. Med kommandot bcp och BULK INSERT-instruktionen kan du ange att nullvärden ska behållas.

En vanlig INSERT-instruktion behåller däremot null-värdet i stället för att infoga ett standardvärde. INFOGA ... SELECT * FROM OPENROWSET(BULK...)-instruktionen ger samma grundläggande beteende som en vanlig INSERT, men stöder dessutom en tabellhint för införande av standardvärden.

Kontur

Behålla null-värden Använder standardvärden med INSERT ... VÄLJ * FRÅN OPENROWSET(BULK...) Exempel på testvillkor  ● exempeltabell  ● exempeldatafil  ● exempelfil i icke-XML-format behålla nullvärden eller använda standardvärden under massimport  ● Använda bcp och behålla null-värden utan formatfil  ● Använda bcp och behålla null-värden med ett filformat som inte är XML  ● Använda bcp och Använda standardvärden utan en formatfil  ● Använda bcp och använda standardvärden med en fil som inte är XML-format  ● Använda BULK INSERT och behålla nullvärden utan formatfil  ● Använda BULK INSERT och behålla null-värden med en icke-XML formatfil  ● Använda BULK INSERT och använda standardvärden utan en formatfil  ● Använda BULK INSERT och standardvärden med en fil som inte är i XML-format  ● Använda OPENROWSET(BULK...) och behålla nullvärden med en fil inte i XML-format  ● Använda OPENROWSET(BULK...) och Använda standardvärden med en icke-XML-formatfil

Behålla null-värden

Följande kvalificerare anger att ett tomt fält i datafilen behåller sitt null-värde under massimportåtgärden, i stället för att ärva ett standardvärde (om det finns något) för tabellkolumnerna. För OPENROWSETgäller som standard att alla kolumner som inte specificeras i massinläsningsåtgärden anges som NULL.

Befallning	Kvalificerare	Typ av kvalificerare
Bcp	-k	Strömbrytare
Massinföring	KEEPNULLS*	Argument
INFOGA... VÄLJ * FRÅN OPENROWSET(BULK...)	Ej tillämpligt	Ej tillämpligt

* Om standardvärdena inte är tillgängliga för BULK INSERT-måste tabellkolumnen definieras för att tillåta null-värden.

Not

Dessa kvalificerare inaktiverar kontrollen av standarddefinitioner i en tabell genom dessa massimportkommandon. För alla samtidiga INSERT-instruktioner förväntas standarddefinitioner.

Använda standardvärden med INSERT ... VÄLJ * FRÅN OPENROWSET(BULK...)

Du kan ange att för ett tomt fält i datafilen använder motsvarande tabellkolumn dess standardvärde (om det finns något). Om du vill använda standardvärden använder du tabelltipset KEEPDEFAULTS.

Note

Mer information finns i INSERT (Transact-SQL), SELECT (Transact-SQL), OPENROWSET (Transact-SQL)och Table Hints (Transact-SQL)

Exempel på testvillkor

Exemplen i det här avsnittet baseras på tabellen, datafilen och formatfilen som definieras nedan.

exempeltabell

Skriptet nedan skapar en testdatabas och en tabell med namnet myNulls. Observera att den fjärde tabellkolumnen, Kids, har ett standardvärde. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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
   );

exempeldatafil

Använd Anteckningar, skapa en tom fil D:\BCP\myNulls.bcp och infoga data nedan. Observera att det inte finns något värde i den tredje posten, den fjärde kolumnen.

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

Du kan också köra följande PowerShell-skript för att skapa och fylla i datafilen:

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;

exempelfil som inte är XML-format

SQL Server stöder två typer av formatfiler: icke-XML-format och XML-format. Icke-XML-format är det ursprungliga format som stöds av tidigare versioner av SQL Server. Mer information finns i SQL Server(Non-XML Format Files). Följande kommando använder verktyget bcp för att generera en fil som inte är xml-format, myNulls.fmt, baserat på schemat för myNulls. Om du vill använda kommandot bcp för att skapa en formatfil anger du argumentet format och använder nul i stället för en datafilsökväg. Formatalternativet kräver också alternativet -f. I det här exemplet används dessutom c för att ange teckendata, t, används för att ange ett kommatecken som en fältavgränsare, och T används för att ange en betrodd anslutning med integrerad säkerhet. I en kommandotolk anger du följande kommando:

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

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

Viktig

Se till att icke-XML-formatfilen slutar med en vagnretur\radmatning. Annars får du troligen följande felmeddelande:

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

Mer information om hur du skapar formatfiler finns i Skapa en formatfil (SQL Server).

Behåll nullvärden eller använd standardvärden under massimport

I exemplen nedan används databasen, datafilen och formatfilerna som skapades ovan.

Använd bcp och behåll nullvärden utan

-k switch. I en kommandotolk anger du följande kommando:

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;"

Använda bcp- och behålla null-värden med en fil som inte är XML-format

-k och -f växlar. I en kommandotolk anger du följande kommando:

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;"

Använda bcp och Använda Standardvärden utan en Formatfil

I en kommandotolk anger du följande kommando:

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;"

Använda bcp och använda standardvärden med en fil som inte är XML-format

-f omkopplare. I en kommandotolk anger du följande kommando:

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;"

Använda BULK INSERT och behålla null-värden utan en formatfil

argumentet keepnulls. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Använda BULK INSERT och behålla null-värden med en fil i icke-XML-format

KEEPNULLS och argumentet FORMATFILE. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Använda BULK INSERT och använda standardvärden utan en formatfil

Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Använda BULK INSERT och och Använda standardvärden med en icke-XML-formatfil

FORMATFILE argument. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Använda OPENROWSET(BULK...) och behålla null-värden med en fil som inte är XML-format

FORMATFILE argument. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Använda OPENROWSET(BULK...) och Använda standardvärden med en fil som inte är XML-format

KEEPDEFAULTS tabelltips och FORMATFILE- argument. Kör följande Transact-SQL i Microsoft SQL Server Management Studio (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;

Relaterade uppgifter

• behåll identitetsvärden vid massimport av data (SQL Server)

• Förbereda data för massexport eller import (SQL Server)

Om du vill använda en formatfil

• Skapa en formatfil (SQL Server)

• Använda en formatfil för att massimportera data (SQL Server)

• Använda en formatfil för att mappa tabellkolumner till Data-File fält (SQL Server)

• Använda en formatfil för att hoppa över ett datafält (SQL Server)

• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)

Om du vill använda dataformat för massimport eller massexport

• Importera Ursprungliga Data och Teckenformat från Tidigare Versioner av SQL Server

• Använda teckenformat för att importera eller exportera data (SQL Server)

• använda internt format för att importera eller exportera data (SQL Server)

• använda Unicode-teckenformat för att importera eller exportera data (SQL Server)

• Använda unicode-inbyggt format för att importera eller exportera data (SQL Server)

Ange dataformat för kompatibilitet när du använder bcp

• Ange fält- och radavgränsare (SQL Server)

• Ange prefixlängd i datafiler med hjälp av bcp (SQL Server)

• Ange fillagringstyp med hjälp av bcp (SQL Server)

Se även

SÄKERHETSKOPIA (Transact-SQL)
OPENROWSET (Transact-SQL)
bcp Verktyg
BULK INSERT (Transact-SQL)
Tabelltips (Transact-SQL)