OPENROWSET BULK(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 의 SQL 분석 엔드포인트Microsoft Fabric의 웨어하우스Microsoft Fabric의 SQL 데이터베이스

함수는 OPENROWSET 하나 이상의 파일에서 데이터를 읽고 콘텐츠를 행 집합으로 반환합니다. 서비스에 따라 파일이 Azure Blob Storage, Azure Data Lake Storage, 온-프레미스 디스크, 네트워크 공유 등에 저장될 수 있습니다. 텍스트/CSV, Parquet 또는 JSON 줄과 같은 다양한 파일 형식을 읽을 수 있습니다.

함수는 OPENROWSET 테이블 이름인 것처럼 쿼리 절에서 FROM 참조할 수 있습니다. 문에서 SELECT 데이터를 읽거나 , , UPDATEINSERT, DELETEMERGE또는 CTAS 문에서 CETAS대상 데이터를 업데이트하는 데 사용할 수 있습니다.

• OPENROWSET(BULK) 는 외부 데이터 파일에서 데이터를 읽기 위해 설계되었습니다.
• OPENROWSET BULK 은 다른 데이터베이스 엔진에서 읽기 위해 설계되었습니다. 자세한 내용은 OPENROWSET(Transact-SQL)을 참조하세요.

이 문서와 이 문서의 인수 집합은 OPENROWSET(BULK) 플랫폼마다 다릅니다.

• Microsoft Fabric 구문의 경우 버전 드롭다운 목록에서 패브릭을 선택합니다.
• SQL Server, Azure SQL Database 및 Azure SQL Managed Instance 구문의 경우 버전 드롭다운 목록에서 플랫폼을 선택합니다.

다른 플랫폼의 유사한 예제에 대한 세부 정보 및 링크:

• Azure SQL Database에 대한 OPENROWSET 자세한 내용은 Azure SQL Database를 사용한 데이터 가상화를 참조하세요.
• Azure SQL Managed Instance에 대한 OPENROWSET 자세한 내용은 Azure SQL Managed Instance를 사용한 데이터 가상화를 참조하세요.
• Azure Synapse의 서버리스 SQL 풀에 대한 정보 및 예제는 Azure Synapse Analytics에서 서버리스 SQL 풀을 사용하여 OPENROWSET을 사용하는 방법을 참조 하세요.
• Azure Synapse의 전용 SQL 풀은 이 함수를 OPENROWSET 지원하지 않습니다.

Transact-SQL 구문 표기 규칙

문법

SQL Server, Azure SQL 데이터베이스, Fabric 내 SQL 데이터베이스, Azure SQL 관리 인스턴스에 대해:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Arguments

옵션의 BULK 인수를 사용하면 데이터 읽기 시작 및 종료 위치, 오류 처리 방법 및 데이터 해석 방법을 크게 제어할 수 있습니다. 예를 들어 데이터 파일을 varbinary, varchar 또는 nvarchar 형식의 단일 행, 단일 열 행 집합으로 읽는 것을 지정할 수 있습니다. 기본 동작에 대한 설명은 그 다음에 나오는 인수 설명을 따릅니다.

이 옵션을 사용하는 BULK 방법에 대한 자세한 내용은 이 문서의 뒷부 분에 있는 설명 섹션을 참조하세요. 옵션에 필요한 사용 권한 BULK 에 대한 자세한 내용은 이 문서의 뒷부분에 있는 사용 권한 섹션을 참조하세요.

대량 가져오기를 위한 데이터를 준비하는 방법에 대한 자세한 내용은 대량 내보내기 또는 가져오기를 위한 데이터 준비를 참조 하세요.

벌크 'data_file_path'

데이터를 읽고 행 집합으로 반환할 데이터 파일의 경로 또는 URI입니다.

URI는 Azure Data Lake Storage 또는 Azure Blob Storage를 참조할 수 있습니다. 데이터를 읽고 행 집합으로 반환할 데이터 파일의 URI입니다.

지원되는 경로 형식은 다음과 같습니다.

• <drive letter>:\<file path> 로컬 디스크의 파일에 액세스하려면
• \\<network-share\<file path> 네트워크 공유의 파일에 액세스하려면
• adls://<container>@<storage>.dfs.core.windows.net/<file path> Azure Data Lake Storage에 액세스하려면
• abs://<storage>.blob.core.windows.net/<container>/<file path> Azure Blob Storage에 액세스하려면
• s3://<ip-address>:<port>/<file path> s3 호환 스토리지에 액세스하려면

Note

이 문서와 지원되는 URI 패턴은 플랫폼마다 다릅니다. Microsoft Fabric Data Warehouse에서 사용할 수 있는 URI 패턴의 경우 버전 드롭다운 목록에서 패브릭을 선택합니다.

SQL Server 2017(14.x)부터 data_file Azure Blob Storage에 있을 수 있습니다. 예제는 Azure Blob Storage의 데이터에 대한 대량 액세스 예제를 참조 하세요.

URI는 문자 시퀀스와 일치하도록 문자를 포함 * 할 수 있으므로 OPENROWSET URI에 대해 패턴 일치를 허용합니다. 또한 모든 하위 폴더를 통해 재귀 순회를 사용하도록 설정할 수 있습니다 /** . SQL Server에서 이 동작은 SQL Server 2022(16.x)부터 사용할 수 있습니다.

다음은 그 예입니다.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

URI에서 참조할 수 있는 스토리지 유형은 다음 표에 나와 있습니다.

버전	On-premises	Azure Storage	Fabric의 OneLake	S3	GCS(Google Cloud)
SQL Server 2017(14.x), SQL Server 2019(15.x)	Yes	Yes	아니오	아니오	아니오
SQL Server 2022(16.x)	Yes	Yes	아니오	Yes	아니오
Azure SQL 데이터베이스	아니오	Yes	아니오	아니오	아니오
Azure SQL 관리형 인스턴스	아니오	Yes	아니오	아니오	아니오
Azure Synapse Analytics의 서버리스 SQL 풀	아니오	Yes	Yes	아니오	아니오
Microsoft Fabric Warehouse 및 SQL 분석 엔드포인트	아니오	Yes	Yes	네, OneLake in Fabric 단축키를 사용하고 있습니다	네, OneLake in Fabric 단축키를 사용하고 있습니다
Microsoft Fabric의 SQL 데이터베이스	아니오	네, OneLake in Fabric 단축키를 사용하고 있습니다	Yes	네, OneLake in Fabric 단축키를 사용하고 있습니다	네, OneLake in Fabric 단축키를 사용하고 있습니다

DATA_SOURCE

DATA_SOURCE 는 데이터 파일 경로의 루트 위치를 정의합니다. BULK 경로에서 상대 경로를 사용할 수 있습니다. 데이터 원본은 CREATE EXTERNAL DATA SOURCE를 사용하여 만들어집니다.

루트 위치 외에도 해당 위치의 파일에 액세스하는 데 사용할 수 있는 사용자 지정 자격 증명을 정의할 수 있습니다.

다음은 그 예입니다.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

파일 형식 옵션

CODEPAGE

데이터 파일에서 데이터의 코드 페이지를 지정합니다. CODEPAGE 는 문자 값이 127보다 많거나 32보다 작은 문자, varchar 또는 텍스트 열이 데이터에 포함된 경우에만 관련이 있습니다. 유효한 값은 'ACP', 'OEM', 'RAW' 또는 'code_page'입니다.

CODEPAGE 값	Description
ACP	문자, varchar 또는 텍스트 데이터 형식의 열을 ANSI/Microsoft Windows 코드 페이지(ISO 1252)에서 SQL Server 코드 페이지로 변환합니다.
OEM(기본값)	문자, varchar 또는 텍스트 데이터 형식의 열을 시스템 OEM 코드 페이지에서 SQL Server 코드 페이지로 변환합니다.
RAW	코드 페이지 간 변환이 일어나지 않습니다. 가장 빠른 옵션입니다.
code_page	데이터 파일의 문자 데이터가 인코딩된 원본 코드 페이지(예: 850)를 나타냅니다.

Important

SQL Server 2016 이전 버전(13.x)은 코드 페이지 65001(UTF-8 인코딩)을 지원하지 않습니다. CODEPAGE 는 Linux에서 지원되는 옵션이 아닙니다.

Note

데이터 정렬/코드 페이지 사양보다 65001 옵션에 더 높은 우선 순위를 두려는 경우를 제외하고는 서식 파일의 각 열에 대한 데이터 정렬 이름을 지정하는 것이 좋습니다.

DATAFILETYPE

OPENROWSET(BULK) 단일 바이트(ASCII, UTF8) 또는 다중 바이트(UTF16) 파일 콘텐츠를 읽어야 되도록 지정합니다. 유효한 값은 char 및 widechar입니다.

DATAFILETYPE 값	모든 데이터 표시 형식
char (기본값)	문자 형식입니다. 자세한 내용은 문자 형식을 사용하여 데이터가져오거나 내보내기를 참조하세요.
widechar	유니코드 문자입니다. 자세한 내용은 유니코드 문자 형식을 사용하여 데이터가져오거나 내보내기를 참조하세요.

FORMAT

참조된 파일의 형식을 지정합니다. 예를 들면 다음과 같습니다.

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

유효한 값은 버전에 따라 'CSV'( RFC 4180 표준과 호환되는 쉼표로 구분된 값 파일), 'PARQUET', 'DELTA'(버전 1.0) 및 'JSONL'입니다.

버전	CSV	PARQUET	DELTA	JSONL
SQL Server 2017(14.x), SQL Server 2019(15.x)	Yes	아니오	아니오	아니오
SQL Server 2022(16.x) 이상 버전	Yes	Yes	Yes	아니오
Azure SQL 데이터베이스	Yes	Yes	Yes	아니오
Azure SQL 관리형 인스턴스	Yes	Yes	Yes	아니오
Azure Synapse Analytics의 서버리스 SQL 풀	Yes	Yes	Yes	아니오
Microsoft Fabric Warehouse 및 SQL 분석 엔드포인트	Yes	Yes	아니오	Yes
Microsoft Fabric의 SQL 데이터베이스	Yes	Yes	아니오	아니오

FORMATFILE

서식 파일의 전체 경로를 지정합니다. SQL Server에서는 다음과 같은 두 가지 유형의 형식 파일을 지원합니다. XML 및 비 XML.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

서식 파일은 결과 집합에서 열 유형을 정의하는 데 필요합니다. 유일한 예외는 언제 SINGLE_CLOB, SINGLE_BLOB또는 SINGLE_NCLOB 지정된 경우입니다. 이 경우 서식 파일이 필요하지 않습니다.

서식 파일에 대한 자세한 내용은 서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)를 참조하세요.

SQL Server 2017(14.x)부터 format_file_path Azure Blob Storage에 있을 수 있습니다. 예제는 Azure Blob Storage의 데이터에 대한 대량 액세스 예제를 참조 하세요.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE 는 형식 파일 경로의 루트 위치를 정의합니다. FORMATFILE 옵션에서 상대 경로를 사용할 수 있습니다.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

형식 파일 데이터 원본은 CREATE EXTERNAL DATA SOURCE를 사용하여 만들어집니다. 루트 위치 외에도 해당 위치의 파일에 액세스하는 데 사용할 수 있는 사용자 지정 자격 증명을 정의할 수 있습니다.

텍스트/CSV 옵션

ROWTERMINATOR

예를 들어 char 및 widechar 데이터 파일에 사용할 행 종결자를 지정합니다.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

기본 행 종결자는 \r\n(줄바꿈 문자)입니다. 자세한 내용은 필드 및 행 종결자 지정을 참조하세요.

FIELDTERMINATOR

char 및 widechar 데이터 파일에 사용할 필드 종결자를 지정합니다. 예를 들면 다음과 같습니다.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

기본 필드 종결자는 (쉼표)입니다 , . 자세한 내용은 필드 및 행 종결자지정을 참조하세요. 예를 들어 파일에서 탭으로 구분된 데이터를 읽으려면 다음을 수행합니다.

FIELDQUOTE = 'field_quote'

SQL Server 2017(14.x)부터 이 인수는 다음 뉴욕 예제와 같이 CSV 파일에서 따옴표 문자로 사용되는 문자를 지정합니다.

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

이 옵션의 값으로 단일 문자만 지정할 수 있습니다. 지정하지 않으면 " 표준에 정의된 따옴표 문자()가 따옴표 문자로 사용됩니다. 문자(예: 쉼표)는 FIELDTERMINATOR 필드 따옴표 안에 배치할 수 있으며 문자로 래핑된 셀에서 일반 문자로 FIELDQUOTE 간주됩니다.

예를 들어 이전 뉴욕 샘플 CSV 데이터 세트를 읽으려면 .를 사용합니다 FIELDQUOTE = '"'. 주소 필드의 값은 (따옴표) 문자 내 " 의 쉼표로 여러 값으로 분할되지 않고 단일 값으로 유지됩니다.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

오류 처리 옵션

ERRORFILE = 'file_name'

형식 오류가 있어 OLE DB 행 집합으로 변환할 수 없는 행을 수집하는 데 사용되는 파일을 지정합니다. 이러한 행은 데이터 파일에서 "있는 그대로" 이 오류 파일에 복사됩니다.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

오류 파일은 명령이 실행될 때 생성됩니다. 파일이 이미 있는 경우 오류가 발생합니다. 또한 확장명이 .ERROR.txt인 제어 파일이 생성됩니다. 이 파일은 오류 파일의 각 행을 참조하여 오류를 진단합니다. 오류가 수정되면 데이터를 로드할 수 있습니다.

SQL Server 2017(14.x)부터 error_file_path는 Azure Blob Storage에 있을 수 있습니다.

ERRORFILE_DATA_SOURCE

SQL Server 2017(14.x)부터 이 인수는 가져오는 동안 발견된 오류가 포함된 오류 파일의 위치를 가리키는 명명된 외부 데이터 원본입니다.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

자세한 내용은 CREATE EXTERNAL DATA SOURCE(Transact-SQL)를 참조하세요.

맥스에이즈 = maximum_errors

형식 파일에 정의된 대로 예외를 throw하기 전에 OPENROWSET 발생할 수 있는 구문 오류 또는 형식이 잘못된 행의 최대 수를 지정합니다. 도달할 MAXERRORS 때까지 OPENROWSET 각 잘못된 행을 무시하고 로드하지 않고 잘못된 행을 하나의 오류로 계산합니다.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

maximum_errors 기본값은 10입니다.

Note

MAX_ERRORS은 제약 조건에 적용되지 않거나 CHECK 및 bigint 데이터 형식을 변환하는 데 적용되지 않습니다.

데이터 처리 옵션

1줄 = first_row

로드할 첫 번째 행의 번호를 지정합니다. 기본값은 1입니다. 지정한 데이터 파일의 첫 번째 행을 나타냅니다. 행 번호는 행 종결자를 계산하여 결정됩니다. FIRSTROW 은 1부터 시작하는 것입니다.

라스트로우 = last_row

로드할 마지막 행의 번호를 지정합니다. 기본값은 0입니다. 지정한 데이터 파일의 마지막 행을 나타냅니다.

ROWS_PER_BATCH = rows_per_batch

데이터 파일의 대략적인 데이터 행 수를 지정합니다. 이 값은 예상치이며 실제 행 수의 근사치(크기 1순위 이내)여야 합니다. 기본적으로 ROWS_PER_BATCH 파일 특성(파일 수, 파일 크기, 반환된 데이터 형식의 크기)에 따라 추정됩니다. 지정은 ROWS_PER_BATCH = 0 생략하는 ROWS_PER_BATCH것과 같습니다. 다음은 그 예입니다.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

순서 ( { 열 [ ASC | DESC ] } [ ,... n ] [ 유니크 ] )

데이터 파일의 데이터를 정렬하는 방법을 지정하는 선택적 힌트입니다. 기본적으로 대량 작업은 데이터 파일이 정렬되지 않았음을 전제로 합니다. 쿼리 최적화 프로그램이 순서를 활용하여 보다 효율적인 쿼리 계획을 생성할 수 있는 경우 성능이 향상될 수 있습니다. 다음 목록에서는 정렬을 지정할 때 도움이 될 수 있는 예제를 제공합니다.

• 클러스터형 인덱스를 포함하는 테이블에 행을 삽입하는 경우. 이 경우 행 집합 데이터가 클러스터형 인덱스 키에 따라 정렬됩니다.
• 다른 테이블과 행 집합을 조인하는 경우. 이 경우 정렬 열과 조인 열이 일치합니다.
• 정렬 열에 따라 행 집합 데이터를 집계하는 경우
• 정렬 및 조인 열이 일치하는 쿼리 절에서 FROM 행 집합을 원본 테이블로 사용합니다.

UNIQUE

데이터 파일에 중복 항목이 없으면 지정합니다.

데이터 파일의 실제 행이 지정된 순서에 따라 정렬되지 않거나 힌트가 지정되고 중복 키가 있는 경우 UNIQUE 오류가 반환됩니다.

열 별칭은 사용되는 경우 ORDER 필요합니다. 열 별칭 목록은 절에서 액세스 BULK 하는 파생 테이블을 참조해야 합니다. 절에 ORDER 지정된 열 이름은 이 열 별칭 목록을 참조합니다. 큰 값 형식(varchar(max), nvarchar(max), varbinary(max) 및 xml) 및 LOB(Large Object) 형식(텍스트, ntext 및 이미지) 열을 지정할 수 없습니다.

콘텐츠 옵션

SINGLE_BLOB

data_file 내용을 varbinary(max) 형식의 단일 행, 단일 열 행 집합으로 반환합니다.

Important

모든 Windows 인코딩 변환만 지원하므로 이 옵션은 SINGLE_BLOBSINGLE_CLOB 대신 XML 데이터만 SINGLE_NCLOBSINGLE_BLOB 가져오는 것이 좋습니다.

SINGLE_CLOB

data_file ASCII로 읽어 현재 데이터베이스의 데이터 정렬을 사용하여 varchar(max) 형식의 단일 행, 단일 열 행 집합으로 내용을 반환합니다.

SINGLE_NCLOB

data_file 유니코드로 읽어 현재 데이터베이스의 데이터 정렬을 사용하여 콘텐츠를 nvarchar(max) 형식의 단일 행, 단일 열 행 집합으로 반환합니다.

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Permissions

OPENROWSET 외부 데이터 원본을 사용하려면 다음 권한이 필요합니다.

• ADMINISTER DATABASE BULK OPERATIONS 또는
• ADMINISTER BULK OPERATIONS

다음 T-SQL 예제는 보안 주체에 부여합니다 ADMINISTER DATABASE BULK OPERATIONS .

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

대상 스토리지 계정이 프라이빗인 경우 보안 주체는 컨테이너 또는 스토리지 계정 수준에서 할당된 Storage Blob 데이터 판독 기 역할(또는 그 이상)도 있어야 합니다.

Remarks

• FROM 함께 SELECT 사용되는 절은 전체 OPENROWSET(BULK...) 기능을 사용하여 테이블 이름 대신 호출 SELECT 할 수 있습니다.

• OPENROWSET 옵션이 있는 BULK에는 FROM 절에 범위 변수 또는 별칭이라고도 하는 상관 관계 이름이 필요합니다. 오류 Msg 491에 결과를 추가 AS <table_alias> 하지 못했습니다. "from 절의 대량 행 집합에 상관 관계 이름을 지정해야 합니다."

• 열 별칭을 지정할 수 있습니다. 열 별칭 목록을 지정하지 않으면 서식 파일에 열 이름이 있어야 합니다. 열 별칭을 지정하면 서식 파일의 열 이름은 무시됩니다. 다음 예를 참조하십시오.

  • FROM OPENROWSET(BULK...) AS table_alias
  • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

• SELECT...FROM OPENROWSET(BULK...) 문은 데이터를 테이블로 가져오지 않고 파일의 데이터를 직접 쿼리합니다.

• 문은 SELECT...FROM OPENROWSET(BULK...) 서식 파일을 사용하여 열 이름과 데이터 형식을 지정하여 대량 열 별칭을 나열할 수 있습니다.

• 또는 OPENROWSET(BULK...) 문에서 INSERT 원본 테이블로 사용하면 MERGE 데이터 파일의 데이터를 테이블로 대량으로 가져옵니다. 자세한 내용은 BULK INSERT 또는 OPENROWSET(BULK...) 사용을 참조 하여 SQL Server로 데이터를 가져옵니다.
• OPENROWSET BULK 이 옵션을 문과 함께 INSERT 사용하면 절에서 BULK 테이블 힌트를 지원합니다. 또한 TABLOCK과 같은 일반적인 테이블 힌트 외에도 BULK 절에는 IGNORE_CONSTRAINTS(CHECK 및 FOREIGN KEY 제약 조건만 무시), IGNORE_TRIGGERS, KEEPDEFAULTS 및 KEEPIDENTITY와 같은 특수 테이블 힌트가 허용됩니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.
• INSERT...SELECT * FROM OPENROWSET(BULK...) 문을 사용하는 방법에 대한 자세한 내용은 데이터 대량 가져오기 및 내보내기(SQL Server)를 참조하세요. 대량 가져오기로 수행된 행 삽입 작업이 트랜잭션 로그에 기록되는 경우에 대한 자세한 내용은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하세요.
• 전체 복구 모델을 사용하여 데이터를 가져오는 데 사용되는 경우 로깅을 OPENROWSET (BULK ...) 최적화하지 않습니다.

Note

사용하는 OPENROWSET경우 SQL Server에서 가장을 처리하는 방법을 이해하는 것이 중요합니다. 보안 고려 사항에 대한 자세한 내용은 BULK INSERT 또는 OPENROWSET(BULK...)을 사용하여 SQL Server로 데이터를 가져옵니다.

SQLCHAR, SQLNCHAR 또는 SQLBINARY 데이터 대량 가져오기

OPENROWSET(BULK...)에서는 지정하지 않은 경우 최대 길이 SQLCHAR또는 SQLNCHARSQLBINARY 데이터가 8,000바이트를 초과하지 않는다고 가정합니다. 가져오는 데이터가 8,000바이트를 초과하는 varchar(max), nvarchar(max) 또는 varbinary(max) 개체가 포함된 LOB 데이터 필드에 있는 경우 데이터 필드의 최대 길이를 정의하는 XML 서식 파일을 사용해야 합니다. 최대 길이를 지정하려면 서식 파일을 편집하고 MAX_LENGTH 특성을 선언합니다.

Note

자동으로 생성된 서식 파일은 LOB 필드의 길이 또는 최대 길이를 지정하지 않습니다. 그러나 직접 서식 파일을 편집하고 길이 또는 최대 길이를 지정할 수 있습니다.

SQLXML 문서 대량 내보내기 또는 가져오기

SQLXML 데이터를 대량으로 내보내거나 가져오려면 서식 파일에서 다음 데이터 형식 중 하나를 사용합니다.

데이터 형식	Effect
SQLCHAR 또는 SQLVARYCHAR	데이터는 클라이언트 코드 페이지 또는 데이터 정렬에 내포된 코드 페이지에서 전송됩니다.
SQLNCHAR 또는 SQLNVARCHAR	데이터를 유니코드로 보냅니다.
SQLBINARY 또는 SQLVARYBIN	데이터를 변환하지 않고 보냅니다.

파일 메타데이터 함수

때로는 결과 세트에서 특정 행과 어떤 파일이나 폴더 소스가 연관되는지 알아야 할 수도 있습니다.

함수 filepath 와 filename 를 사용해 파일 이름이나 결과 집합의 경로를 반환할 수 있습니다. 또는 파일 이름이나 폴더 경로에 따라 데이터를 필터링하는 데 사용할 수도 있습니다. 다음 섹션에서는 샘플과 함께 짧은 설명을 찾을 수 있습니다.

파일 이름 기능

이 함수는 해당 행이 시작된 파일 이름을 반환합니다.

반환 데이터 타입은 nvarchar(1024)입니다. 최적의 성능을 위해 항상 파일 이름 함수의 결과를 적절한 데이터 타입으로 캐스트하세요. 문자 데이터 타입을 사용할 경우, 적절한 길이를 사용하는지 확인하세요.

다음 샘플은 2017년 마지막 3개월간의 NYC 옐로우 택시 데이터 파일을 읽고 파일당 탑승 횟수를 반환합니다. 쿼리의 부분은 OPENROWSET 어떤 파일을 읽을지 지정합니다.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

다음 예시는 읽을 파일을 필터링하는 데 절에서 filename() 어떻게 WHERE 사용할 수 있는지 보여줍니다. 쿼리 부분의 OPENROWSET 전체 폴더에 접근하고 절 내 WHERE 파일을 필터링합니다.

결과는 앞선 예시와 같을 것입니다.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

파일패스 함수

이 함수는 전체 경로 또는 경로의 일부를 반환합니다:

• 매개변수 없이 호출되면 해당 행이 시작된 전체 파일 경로를 반환합니다.
• 매개변수로 호출하면 매개변수에 지정된 위치의 와일드카드와 일치하는 경로 일부를 반환합니다. 예를 들어, 매개변수 값 1은 첫 번째 와일드카드와 일치하는 경로 일부를 반환합니다.

반환 데이터 타입은 nvarchar(1024)입니다. 최적의 성능을 위해 항상 함수의 filepath 결과를 적절한 데이터 타입으로 캐스트하세요. 문자 데이터 타입을 사용할 경우, 적절한 길이를 사용하는지 확인하세요.

다음 샘플은 2017년 마지막 3개월간의 NYC 옐로우 택시 데이터 파일을 읽고 있습니다. 파일 경로당 라이딩 횟수를 반환합니다. 쿼리의 부분은 OPENROWSET 어떤 파일을 읽을지 지정합니다.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

다음 예시는 읽을 파일을 필터링하는 데 절에서 filepath() 어떻게 WHERE 사용할 수 있는지 보여줍니다.

쿼리 부분의 OPENROWSET 와일드카드를 사용하고 절 내 WHERE 파일들을 필터링할 수 있습니다. 결과는 앞선 예시와 같을 것입니다.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

이 섹션에서는 구문을 사용하는 OPENROWSET BULK 방법을 보여 주는 일반적인 예제를 제공합니다.

A. OPENROWSET을 사용하여 varbinary(max) 열에 파일 데이터를 대량 삽입

적용 대상: SQL Server만 해당합니다.

다음 예제에서는 데모용으로 작은 테이블을 만들고 루트 디렉터리에 있는 Text1.txt 파일 C: 의 파일 데이터를 varbinary(max) 열에 삽입합니다.

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. 서식 파일과 함께 OPENROWSET BULK 공급자를 사용하여 텍스트 파일에서 행 검색

적용 대상: SQL Server만 해당합니다.

다음 예에서는 서식 파일을 사용하여 다음 데이터가 들어 있는 탭으로 분리된 텍스트 파일인 values.txt에서 행을 검색합니다.

1     Data Item 1
2     Data Item 2
3     Data Item 3

서식 파일인 values.fmt는 values.txt의 열을 설명합니다.

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

이 쿼리는 해당 데이터를 검색합니다.

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. 서식 파일 및 코드 페이지 지정

적용 대상: SQL Server만 해당합니다.

다음 예제에서는 서식 파일 및 코드 페이지 옵션을 동시에 사용하는 방법을 보여 있습니다.

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. 형식 파일을 사용하여 CSV 파일에서 데이터에 액세스

적용 대상: SQL Server 2017(14.x) 이상 버전만 해당합니다.

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. 형식 파일 없이 CSV 파일에서 데이터에 액세스

적용 대상: SQL Server만 해당합니다.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Important

ODBC 드라이버는 64비트여야 합니다. Windows에서 ODBC 데이터 원본에 연결(SQL Server 가져오기 및 내보내기 마법사) 애플리케이션의 드라이버 탭을 열어 이를 확인합니다. 64비 Microsoft Text Driver (*.txt, *.csv) 트 버전에서는 작동하지 않는 32비트가 있습니다 sqlservr.exe.

F. Azure Blob Storage에 저장된 파일에서 데이터에 액세스

적용 대상: SQL Server 2017(14.x) 이상 버전만 해당합니다.

SQL Server 2017(14.x) 이상 버전에서 다음 예제에서는 Azure Storage 계정의 컨테이너를 가리키는 외부 데이터 원본과 공유 액세스 서명에 대해 만든 데이터베이스 범위 자격 증명을 사용합니다.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

자격 증명 및 외부 데이터 원본 구성을 비롯한 전체 OPENROWSET 예제는 Azure Blob Storage의 데이터에 대한 대량 액세스 예제를 참조하세요.

G. Azure Blob Storage에 저장된 파일에서 테이블로 가져오기

다음 예제에서는 명령을 사용하여 OPENROWSET SAS 키를 만든 Azure Blob Storage 위치의 csv 파일에서 데이터를 로드하는 방법을 보여 줍니다. Azure Blob Storage 위치는 외부 데이터 원본으로 구성되었습니다. 이 경우, 사용자 데이터베이스의 마스터 키로 암호화된 공유 액세스 서명을 사용하는 데이터베이스 범위 자격 증명이 필요합니다.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. 외부 원본에 관리 ID 사용

적용 대상: Azure SQL Managed Instance 및 Azure SQL Database

다음 예제에서는 관리 ID를 사용하여 자격 증명을 만들고, 외부 원본을 만든 다음, 외부 원본에 호스트된 CSV에서 데이터를 로드합니다.

먼저 자격 증명을 만들고 Blob 스토리지를 외부 원본으로 지정합니다.

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

다음으로, Blob 스토리지에 호스트된 CSV 파일에서 데이터를 로드합니다.

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. OPENROWSET을 사용하여 S3 호환 개체 스토리지를 사용하여 여러 Parquet 파일에 액세스

적용 대상: SQL Server 2022(16.x) 이상 버전

다음 예제에서는 S3 호환 개체 스토리지에 저장된 여러 위치에서 여러 Parquet 파일에 액세스합니다.

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. OPENROWSET을 사용하여 Azure Data Lake Gen2에서 여러 델타 테이블에 액세스

적용 대상: SQL Server 2022(16.x) 이상 버전

이 예제에서 데이터 테이블 컨테이너의 이름은 ContosoAzure Data Lake Gen2 스토리지 계정에 있습니다.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. OPENROWSET을 사용하여 공용 익명 데이터 세트 쿼리

다음 예제에서는 공개적으로 사용할 수 있는 NYC 노란색 택시 여정 레코드 열기 데이터 집합을 사용합니다.

먼저 데이터 원본을 만듭니다.

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

이름 패턴과 .parquet 일치하는 폴더의 확장명을 가진 모든 파일을 쿼리합니다.

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

추가 예제

사용을 OPENROWSET(BULK...)보여 주는 더 많은 예제는 다음 문서를 참조하세요.

• 데이터 대량 가져오기 및 내보내기(SQL Server)
• XML 문서 대량 가져오기 및 내보내기 예(SQL Server)
• 데이터 대량 가져오기 중 ID 값 유지(SQL Server)
• 대량 가져오기 수행 중 Null 또는 기본값 유지(SQL Server)
• 서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)
• 문자 형식을 사용하여 데이터 가져오기 또는 내보내기(SQL Server)
• 서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)
• 서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server)
• 서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)
• Azure SQL Managed Instances에서 OPENROWSET을 사용하여 데이터 원본 쿼리
• 필드 및 행 종결자 지정(SQL Server)

관련 콘텐츠

• 삭제 (Transact-SQL)
• FROM 절과 JOIN, APPLY, PIVOT(Transact-SQL)
• INSERT(Transact-SQL)
• OPENDATASOURCE(Transact-SQL)
• OPENQUERY(Transact-SQL)
• SELECT(Transact-SQL)
• UPDATE(Transact-SQL)
• 여기서 (Transact-SQL)