서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)

적용 대상: SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)

이 문서에서는 대량 가져오기 작업에서 서식 파일을 사용하는 방법에 대해 설명합니다. 서식 파일은 데이터 파일의 필드를 테이블의 열에 매핑합니다. bcp(SQL Server)를 사용하여 서식 파일 만들기를 참조하십시오.

필수 조건

• SQL Server 인스턴스에서 T-SQL 명령을 실행하려면 SSMS(SQL Server Management Studio),Visual Studio Code용 MSSQL 확장, sqlcmd 또는 즐겨 찾는 T-SQL 쿼리 도구를 사용합니다.

• 유니코드 문자 데이터 파일에 서식 파일을 사용하려면 모든 입력 필드가 유니코드 텍스트 문자열(고정 크기 또는 문자 종료 유니코드 문자열)이어야 합니다.

• XML 문서(SQL Server) 데이터의 대량 가져오기 및 내보내기 예제를 대량으로 내보내거나 가져오려면 서식 파일에서 다음 데이터 형식 중 하나를 사용합니다.

  • SQLCHAR 또는 SQLVARCHAR(데이터를 클라이언트 코드 페이지나 데이터 정렬에 포함된 코드 페이지로 보냅니다.)
  • SQLNCHAR 또는 SQLNVARCHAR(데이터가 유니코드로 전송됨)
  • SQLBINARY 또는 SQLVARBIN(데이터를 전환하지 않고 보냅니다.)

• Azure SQL Database 및 Azure Synapse Analytics는 bcp만 지원합니다. 자세한 내용은 다음을 참조하세요.

  • Azure Synapse Analytics에 데이터 로드
  • SQL Server에서 Azure Synapse Analytics로 데이터 로드(플랫 파일)
  • 데이터 마이그레이션

• 대량 삽입을 포함한 이 구문은 Azure Synapse Analytics에서 지원되지 않습니다. Azure Synapse Analytics 및 기타 클라우드 데이터베이스 플랫폼 통합에서 Azure Data Factory의 COPY 문을 통해 또는 COPY INTO 및 PolyBase와 같은 T-SQL 문을 사용하여 데이터 이동을 수행합니다.

예시 테스트 조건

이 항목의 서식 파일 예제는 아래에 정의된 테이블 및 데이터 파일을 기반으로 합니다.

샘플 테이블

아래 스크립트는 테스트 데이터베이스와 이름이 myFirstImport인 테이블을 만듭니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

CREATE DATABASE TestDatabase;
GO

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

샘플 데이터 파일

Windows 메모장을 사용하여 빈 파일 D:\BCP\myFirstImport.bcp를 만들고 다음 데이터를 삽입합니다.

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

또는 다음 PowerShell 스크립트를 실행하여 데이터 파일을 만들고 채울 수 있습니다.

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;

서식 파일 만들기

SQL Server 은(는) 두 유형의 서식 파일, 즉 비 XML 서식 파일과 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 이전 버전의 SQL Server에서 원래 지원했던 서식 파일입니다.

코드 샘플의 로컬 파일 위치를 컴퓨터의 파일 위치로 변경합니다.

비 XML 서식 파일 만들기

비 XML 형식 파일 사용(SQL Server)를 검토하여 자세한 정보를 확인하십시오. 다음 명령은 bcp 유틸리티를 사용하여 myFirstImport.fmt 스키마에 따라 비 XML 서식 파일 myFirstImport을 생성합니다.

• bcp 명령을 사용하여 서식 파일을 만들려면 인수를 format 지정하고 데이터 파일 경로 대신 사용합니다 nul .
• 서식 옵션에는 -f 옵션도 필요합니다.
• c 는 문자 데이터를 지정하는 데 사용됩니다.
• t,는 필드 종결자로 쉼표 지정에 사용됩니다.
• T 는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용됩니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

비 XML 서식 파일 D:\BCP\myFirstImport.fmt 는 다음과 같이 표시됩니다.

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

중요

비 XML 서식 파일이 캐리지 리턴\줄 바꿈로 끝나는지 확인합니다. 그러지 않으면 다음과 같은 오류 메시지가 표시될 수 있습니다.

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

XML 서식 파일 만들기

자세한 내용은 XML 형식 파일(SQL Server) 을 검토하세요. 다음 명령은 bcp 유틸리티를 사용하여 myFirstImport.xml 스키마에 따라 XML 서식 파일 myFirstImport을 생성합니다

• bcp 명령을 사용하여 서식 파일을 만들려면 인수를 format 지정하고 데이터 파일 경로 대신 사용합니다 nul .
• 서식 옵션에는 -f 항상 옵션이 필요합니다.
• XML 서식 파일을 만들려면 옵션도 지정 -x 해야 합니다.
• c 는 문자 데이터를 지정하는 데 사용됩니다.
• t,는 필드 종결자로 쉼표 지정에 사용됩니다.
• T 는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용됩니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

XML 서식 파일 D:\BCP\myFirstImport.xml은 다음과 같아야 합니다.

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

서식 파일을 사용하여 데이터 대량 가져오기

아래 예제에서는 위에서 만든 데이터베이스, 데이터 파일 및 서식 파일을 사용합니다.

bcp 사용 및 비 XML 서식 파일 사용(SQL Server)

명령 프롬프트에서 다음 명령을 입력합니다.

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"

bcp 및 XML 형식 파일 사용(SQL Server)

명령 프롬프트에서 다음 명령을 입력합니다.

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

BULK INSERT(Transact-SQL) 사용과 비 XML 서식 파일 사용(SQL Server)

Microsoft SSMS(SQL Server Management Studio)에서 다음 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;

BULK INSERT(Transact-SQL) 및 XML 서식 파일 사용(SQL Server)

Microsoft SSMS(SQL Server Management Studio)에서 다음 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;

OPENROWSET BULK(Transact-SQL) 사용하기 및 비 XML 형식 파일 사용(SQL Server)

Microsoft SSMS(SQL Server Management Studio)에서 다음 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;

OPENROWSET BULK(Transact-SQL) 및 XML 형식 파일 사용(SQL Server)

Microsoft SSMS(SQL Server Management Studio)에서 다음 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;

추가 예제

• bcp를 사용하여 서식 파일 만들기(SQL Server)
• 서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)
• 서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server)
• 서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)

관련 콘텐츠

• bcp 유틸리티
• BULK INSERT(Transact-SQL)
• OPENROWSET BULK(Transact-SQL)
• 비 XML 서식 파일 사용(SQL Server)
• XML 형식 파일(SQL Server)
• 데이터를 가져오거나 내보내기 위한 서식 파일(SQL Server)