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 části Vytvoření formátového souboru (SQL Server).

Než začnete

• 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).
• Pokud chcete hromadně exportovat nebo importovat dat SQLXML, použijte ve svém 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.

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

Podrobné informace najdete v části Ne-XML formát souborů (SQL Server). 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í formátu souboru použít příkaz bcp, zadejte formát argumentu a místo cesty k datovému souboru použijte nul. Možnost formátu také vyžaduje možnost -f. V tomto příkladu se kvalifikátor c používá k určení znakových dat, t, slouží k určení čárky jako ukončovače pole , a 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

Prohlédněte si soubory formátu XML (SQL Server) pro podrobné informace. 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í formátu souboru použít příkaz bcp, zadejte formát argumentu a místo cesty k datovému souboru použijte nul. Možnost formátu vždy vyžaduje možnost -f a k vytvoření souboru formátu XML musíte také zadat možnost -x. V tomto příkladu se kvalifikátor c používá k určení znakových dat, t, slouží k zadání čárky jako ukončovacípole a 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žijte bcp a soubor ve formátu bez XML

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žijte formát bcp a soubor ve formátu XML

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 a soubor ve formátu ne-XML

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žijte BULK INSERT a formát souboru XML

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...) a ne-XML formát souboru

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žití OPENROWSET(BULK...) a formát souboru XML

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řit Formátový Soubor (SQL Server)
přeskočit sloupec tabulky (SQL Server) pomocí formátového souboru
Přeskočení datového pole (SQL Server) pomocí formátového souboru
Mapování sloupců tabulky na pole Data-File (SQL Server) pomocí formátového souboru

Další kroky

• bcp nástroj
• hromadné vložení (Transact-SQL)
• OPENROWSET (Transact-SQL)
• soubory formátu NEŽ XML (SQL Server)
• Soubory formátu XML (SQL Server)
• formát souborů pro import nebo export dat (SQL Server)