Použití formátu souboru k hromadnému importu dat (SQL Server)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Tento článek ukazuje použití formátu souboru v operacích hromadného importu. Formátový soubor mapuje pole datového souboru na sloupce tabulky. Další informace najdete v tématu Vytvoření formátu souboru s bcp (SQL Server).

Požadavky

• Pokud chcete spouštět příkazy T-SQL v instanci SQL Serveru, použijte SQL Server Management Studio (SSMS),rozšíření MSSQL pro Visual Studio Code, sqlcmd nebo váš oblíbený nástroj pro dotazování T-SQL.

• Aby formátový soubor fungoval s datovým souborem znaků Unicode, musí být všechna vstupní pole textovými řetězci Unicode (to znamená řetězce Unicode s pevnou velikostí nebo ukončenými znaky).

• K hromadnému exportu nebo importu příkladů hromadného importu a exportu dat dokumentů XML (SQL Server) použijte v souboru formátu jeden z následujících datových typů:

  • SQLCHAR nebo SQLVARCHAR (data se odesílají ve znakovou stránku klienta nebo ve znakové stránce implikované kolací)
  • SQLNCHAR nebo SQLNVARCHAR (data se odesílají jako Unicode)
  • SQLBINARY nebo SQLVARBIN (data se odesílají bez jakéhokoli převodu).

• Azure SQL Database a Azure Synapse Analytics podporují pouze bcp. Další informace najdete tady:

  • načtení dat do Azure Synapse Analytics
  • načtení dat z SQL Serveru do azure Synapse Analytics (ploché soubory)
  • Migrace Vašich Dat

• Tato syntaxe, včetně hromadného vložení, se ve službě Azure Synapse Analytics nepodporuje. V Azure Synapse Analytics a dalších integracích cloudové databázové platformy můžete provádět přesun dat prostřednictvím příkazu COPY ve službě Azure Data Factorynebo pomocí příkazů T-SQL, jako je COPY INTO a PolyBase.

Příklady testovacích podmínek

Příklady souborů formátu v tomto tématu jsou založené na tabulce a datovém souboru definovaném níže.

Ukázková tabulka

Následující skript vytvoří testovací databázi a tabulku s názvem myFirstImport. V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Ukázkový datový soubor

Pomocí Poznámkového bloku vytvořte prázdný soubor D:\BCP\myFirstImport.bcp a vložte následující data:

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

Případně můžete spustit následující skript PowerShellu pro vytvoření a naplnění datového souboru:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.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,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Vytvoření formátových souborů

SQL Server podporuje dva typy formátových souborů: ne-XML formát a XML formát. Formát jiného formátu než XML je původní formát podporovaný staršími verzemi SQL Serveru.

Změňte umístění místních souborů ukázky kódu na umístění souboru na vašem počítači.

Vytvoření souboru jiného formátu než XML

Prostudujte si použití souborů ve formátu, který není XML (SQL Server) pro podrobné informace. Následující příkaz použije nástroj bcp k vygenerování souboru jiného formátu než XML, myFirstImport.fmt, na základě schématu myFirstImport.

• Pokud chcete k vytvoření souboru formátu použít příkaz bcp, zadejte format argument a použijte nul místo cesty k datovému souboru.
• Možnost formátu také vyžaduje možnost -f.
• c slouží k určení znakových dat.
• t, slouží k označení čárky jako ukončovače pole.
• T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení.

Na příkazovém řádku zadejte následující příkaz:

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

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

Soubor ve formátu jiného formátu než XML by měl D:\BCP\myFirstImport.fmt vypadat následovně:

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

Důležitý

Ujistěte se, že soubor bez formátu XML končí znakem return\line. V opačném případě se pravděpodobně zobrazí následující chybová zpráva:

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

Vytvoření souboru formátu XML

Podrobné informace najdete v souborech formátu XML (SQL Server ). Následující příkaz použije nástroj bcp k vytvoření souboru formátu XML, myFirstImport.xml, na základě schématu myFirstImport.

• Pokud chcete k vytvoření souboru formátu použít příkaz bcp, zadejte format argument a použijte nul místo cesty k datovému souboru.
• Možnost formátu vždy vyžaduje -f možnost.
• Chcete-li vytvořit soubor formátu XML, musíte také zadat -x možnost.
• c slouží k určení znakových dat.
• t, slouží k označení čárky jako ukončovače pole.
• T slouží k určení důvěryhodného připojení pomocí integrovaného zabezpečení.

Na příkazovém řádku zadejte následující příkaz:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

Soubor ve formátu XML by měl vypadat D:\BCP\myFirstImport.xml takto:

<?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="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Použití formátu souboru k hromadnému importu dat

Následující příklady používají soubory databáze, datového souboru a formátu vytvořené výše.

Použití souborů formátu bcp a použití souborů bez formátu XML (SQL Server)

Na příkazovém řádku zadejte následující příkaz:

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

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

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

Použití souborů formátu bcp a XML (SQL Server)

Na příkazovém řádku zadejte následující příkaz:

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

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

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

Použijte BULK INSERT (Transact-SQL) a použijte soubory formátu ne-XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

USE TestDatabase;  
GO

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

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

Použití funkce BULK INSERT (Transact-SQL) a souborů formátu XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

USE TestDatabase;  
GO

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

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

Použijte OPENROWSET BULK (Transact-SQL) a použijte soubory ve formátu, který není XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

USE TestDatabase;
GO

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

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

Použijte OPENROWSET BULK (Transact-SQL) a formátovací soubory XML (SQL Server)

V aplikaci Microsoft SQL Server Management Studio (SSMS) spusťte následující Transact-SQL:

USE TestDatabase;  
GO

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

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

Další příklady

• Vytvoření formátu souboru pomocí bcp (SQL Server)
• přeskočit sloupec tabulky (SQL Server) pomocí formátového souboru
• Použití formátu souboru k přeskočení datového pole (SQL Server)
• Mapování sloupců tabulky na pole datových souborů (SQL Server) pomocí formátového souboru

Související obsah

• bcp nástroj
• hromadné vložení (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Použití souborů bez formátu XML (SQL Server)
• Soubory formátu XML (SQL Server)
• Formát souborů pro import nebo export dat (SQL Server)