Udostępnij za pośrednictwem

Używanie pliku formatu do zbiorczego importowania danych (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

W tym artykule przedstawiono użycie pliku formatu w operacjach importowania zbiorczego. Plik formatu mapuje pola pliku danych na kolumny tabeli. Aby uzyskać dodatkowe informacje, przejrzyj Create a format file with bcp (SQL Server) (Tworzenie pliku formatu przy użyciu programu bcp (SQL Server).

Wymagania wstępne

Przykładowe warunki testu

Przykłady plików formatu w tym temacie są oparte na tabeli i pliku danych zdefiniowanym poniżej.

Przykładowa tabela

Poniższy skrypt tworzy testową bazę danych i tabelę o nazwie myFirstImport. Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

Przykładowy plik danych

Za pomocą Notatnika utwórz pusty plik D:\BCP\myFirstImport.bcp i wstaw następujące dane:

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

Alternatywnie możesz wykonać następujący skrypt programu PowerShell, aby utworzyć i wypełnić plik danych:

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;

Tworzenie plików formatu

Program SQL Server obsługuje dwa typy plików formatu: format inny niż XML i format XML. Format inny niż XML jest oryginalnym formatem obsługiwanym przez wcześniejsze wersje programu SQL Server.

Zmień lokalizacje plików lokalnych przykładu kodu na lokalizację pliku na maszynie.

Tworzenie pliku w formacie innego niż XML

Aby uzyskać szczegółowe informacje, zapoznaj się z Użyj plików w formacie non-XML (SQL Server). Następujące polecenie użyje narzędzia bcp do wygenerowania pliku formatu innego niż xml, myFirstImport.fmt, na podstawie schematu myFirstImport.

  • Aby użyć polecenia bcp do utworzenia pliku formatu, określ format argument i użyj nul zamiast ścieżki pliku danych.
  • Opcja formatowania wymaga również opcji -f.
  • c służy do określania danych znaków
  • t, służy do określania przecinka jako terminatora pola
  • T służy do określania zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń.

W wierszu polecenia wprowadź następujące polecenie:

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

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

Plik formatu innego niż XML D:\BCP\myFirstImport.fmt powinien wyglądać następująco:

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

Ważny

Upewnij się, że plik w formacie innym niż XML kończy się powrotem karetki\znakiem nowej linii. W przeciwnym razie prawdopodobnie zostanie wyświetlony następujący komunikat o błędzie:

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

Tworzenie pliku formatu XML

Przejrzyj pliki formatu XML (SQL Server), aby uzyskać szczegółowe informacje. Następujące polecenie użyje narzędzia bcp do utworzenia pliku formatu XML, myFirstImport.xml, na podstawie schematu myFirstImport.

  • Aby użyć polecenia bcp do utworzenia pliku formatu, określ format argument i użyj nul zamiast ścieżki pliku danych.
  • Opcja formatowania zawsze wymaga -f opcji .
  • Aby utworzyć plik formatu XML, należy również określić -x opcję .
  • c służy do określania danych znaków
  • t, służy do określania przecinka jako terminatora pola
  • T służy do określania zaufanego połączenia przy użyciu zintegrowanych zabezpieczeń.

W wierszu polecenia wprowadź następujące polecenie:

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

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

Plik formatu XML D:\BCP\myFirstImport.xml powinien wyglądać następująco:

<?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>

Używanie pliku formatu do zbiorczego importowania danych

W poniższych przykładach użyto bazy danych, pliku danych i plików formatowania utworzonych powyżej.

Używanie plików w formacie bcp i używanie plików formatu innego niż XML (SQL Server)

W wierszu polecenia wprowadź następujące polecenie:

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"

Używanie plików formatu bcp i XML (SQL Server)

W wierszu polecenia wprowadź następujące polecenie:

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

Używanie funkcji BULK INSERT (Transact-SQL) i używanie plików formatu innego niż XML (SQL Server)

Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

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;

Używanie funkcji BULK INSERT (Transact-SQL) i plików formatu XML (SQL Server)

Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

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;

Użyj OPENROWSET BULK (Transact-SQL) i użyj plików w formacie innym niż XML (SQL Server)

Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

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;

Użyj OPENROWSET BULK (Transact-SQL) i plików formatu XML (SQL Server)

Wykonaj polecenie Transact-SQL w programie Microsoft SQL Server Management Studio (SSMS):

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;

Więcej przykładów

Treści powiązane