OPENROWSET(Transact-SQL)

적용 대상:SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance

OLE DB 데이터 원본에서 원격 데이터를 액세스하는 데 필요한 모든 연결 정보를 포함합니다. 이 방법은 OLE DB를 사용하여 원격 데이터에 연결하고 액세스하는 일회성의 임시 방법이며 연결된 서버에서 테이블을 액세스하는 방법의 대안입니다. OLE DB 데이터 원본을 자주 참조하려면 연결된 서버를 사용합니다. 자세한 내용은 연결된 서버(데이터베이스 엔진)를 참조하세요. 함수는 OPENROWSET 테이블 이름인 것처럼 쿼리 절에서 FROM 참조할 수 있습니다. OPENROWSET 함수는 OLE DB 공급자의 기능에 따라 INSERT, UPDATE 또는 DELETE 문의 대상 테이블로 참조될 수도 있습니다. 쿼리는 여러 결과 집합을 반환할 수 있지만 OPENROWSET은 첫 번째 결과 집합만 반환할 수 있습니다.

OPENROWSET은 파일의 데이터를 읽어서 행 집합으로 반환할 수 있는 기본 제공 BULK 공급자를 통해 대량 작업을 지원합니다.

이 문서의 많은 예제는 SQL Server에만 적용됩니다. 다른 플랫폼의 유사한 예제에 대한 세부 정보 및 링크:

• Azure SQL Database는 Azure Blob Storage에서 읽기만 지원합니다.
• Azure SQL Managed Instance에 대한 예제는 OPENROWSET을 사용하여 데이터 원본 쿼리를 참조하세요.
• Azure Synapse의 서버리스 SQL 풀에 대한 정보 및 예제는 Azure Synapse Analytics에서 서버리스 SQL 풀을 사용하여 OPENROWSET을 사용하는 방법을 참조 하세요.
• Azure Synapse의 전용 SQL 풀은 이 함수를 OPENROWSET 지원하지 않습니다.

Transact-SQL 구문 표기 규칙

구문

OPENROWSET 구문은 외부 데이터 원본을 쿼리하는 데 사용됩니다.

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

OPENROWSET(BULK) 구문은 외부 파일을 읽는 데 사용됩니다.

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_characters' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]

   [ , MAXERRORS = maximum_errors ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]

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

인수

일반적인 인수

'provider_name'

레지스트리에 지정된 대로 OLE DB 공급자의 이름(또는 PROGID)을 나타내는 문자열입니다. provider_name에는 기본값이 없습니다. 공급자 이름의 예는 Microsoft.Jet.OLEDB.4.0, SQLNCLI, MSDASQL입니다.

'datasource'

특정 OLE DB 데이터 원본에 해당하는 문자열 상수입니다. datasource 는 공급자를 DBPROP_INIT_DATASOURCE 초기화하기 위해 IDBProperties 공급자의 인터페이스에 전달할 속성입니다. 일반적으로 이 문자열에는 데이터베이스 파일의 이름, 데이터베이스 서버 이름 또는 공급자가 데이터베이스 또는 데이터베이스를 찾기 위해 이해하는 이름이 포함됩니다.

데이터 원본은 C:\SAMPLES\Northwind.mdb' 공급자의 경우 파일 경로 Microsoft.Jet.OLEDB.4.0이거나 Server=Seattle1;Trusted_Connection=yes; 공급자의 경우 연결 문자열 SQLNCLI일 수 있습니다.

'user_id'

지정된 OLE DB 공급자에 전달된 사용자 이름인 문자열 상수입니다. user_id 연결에 대한 보안 컨텍스트를 지정하고 공급자를 초기화하는 속성으로 DBPROP_AUTH_USERID 전달됩니다. user_id Microsoft Windows 로그인 이름이 될 수 없습니다.

'password'

OLE DB 공급자에 전달할 사용자 암호인 문자열 상수입니다. 암호 는 공급자를 초기화할 DBPROP_AUTH_PASSWORD 때 속성으로 전달됩니다. 암호 는 Microsoft Windows 암호일 수 없습니다.

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    'admin';
    'password',
    Customers
) AS a;

'provider_string'

OLE DB 공급자를 초기화하는 속성으로 DBPROP_INIT_PROVIDERSTRING 전달되는 공급자별 연결 문자열. provider_string은 일반적으로 공급자를 초기화하는 데 필요한 모든 연결 정보를 캡슐화합니다. SQL Server Native Client OLE DB 공급자가 인식하는 키워드 목록은 초기화 및 권한 부여 속성(Native Client OLE DB 공급자)을 참조하세요.

SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

<table_or_view>

OPENROWSET이 읽어야 하는 데이터를 포함하는 원격 테이블 또는 뷰입니다. 이름이 다음과 같은 3개의 구성 요소로 이루어진 개체일 수 있습니다.

• catalog(선택 사항) - 지정한 개체가 있는 카탈로그 또는 데이터베이스의 이름입니다.
• schema(선택 사항) - 지정한 개체에 대한 스키마 또는 개체 소유자의 이름입니다.
• object - 작업할 개체를 고유하게 식별하는 개체 이름입니다.

SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'query'

공급자가 보내고 실행하는 문자열 상수입니다. SQL Server의 로컬 인스턴스는 이 쿼리를 처리하지 않지만 통과 쿼리인 공급자가 반환한 쿼리 결과를 처리합니다. 통과 쿼리는 테이블 이름을 통해 테이블 형식 데이터를 사용할 수 없지만 명령 언어를 통해서만 사용할 수 있는 공급자에서 사용할 때 유용합니다. 쿼리 공급자가 OLE DB 명령 개체와 해당 필수 인터페이스를 지원하는 경우에는 원격 서버에서 통과 쿼리를 사용할 수 있습니다. 자세한 내용은 SQL Server Native Client(OLE DB) 인터페이스를 참조하세요.

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

BULK 인수

BULK 행 집합 공급자를 OPENROWSET 사용하여 파일에서 데이터를 읽습니다. SQL Server OPENROWSET 에서 대상 테이블에 데이터를 로드하지 않고 데이터 파일에서 읽을 수 있습니다. 이렇게 하면 기본 OPENROWSET 문과 함께 사용할 SELECT 수 있습니다.

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

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

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

BULK ‘data_file’

대상 테이블에 데이터를 복사할 데이터 파일의 전체 경로입니다.

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

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

BULK 오류 처리 옵션

ERRORFILE = 'file_name'

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

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

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

ERRORFILE_DATA_SOURCE_NAME

SQL Server 2017(14.x)부터 이 인수는 가져오는 동안 발견된 오류를 포함하는 오류 파일의 Azure Blob Storage 위치를 가리키는 명명된 외부 데이터 원본입니다. 를 사용하여 외부 데이터 원본을 TYPE = BLOB_STORAGE만들어야 합니다. 자세한 내용은 CREATE EXTERNAL DATA SOURCE를 참조하세요.

MAXERRORS = maximum_errors

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

maximum_errors의 기본값은 10입니다.

BULK 데이터 처리 옵션

FIRSTROW = first_row

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

LASTROW = last_row

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

ROWS_PER_BATCH = rows_per_batch

데이터 파일의 대략적인 데이터 행 수를 지정합니다. 이 값은 실제 행 수와 순서가 같아야 합니다.

OPENROWSET은 데이터 파일을 항상 단일 일괄 처리로 가져옵니다. 그러나 0보다 큰 값으로 rows_per_batch를 지정하면 쿼리 프로세서가 >의 값을 힌트로 사용하여 리소스를 쿼리 계획에 할당합니다.

기본적으로 알 ROWS_PER_BATCH 수 없습니다. 지정은 ROWS_PER_BATCH = 0 생략하는 ROWS_PER_BATCH것과 같습니다.

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

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

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

UNIQUE

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

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

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

SINGLE_BLOB

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

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;

BULK 입력 파일 형식 옵션

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

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

CODEPAGE 값	설명
ACP	char, varchar 또는 text 데이터 형식의 열을 ANSI/Microsoft Windows 코드 페이지(ISO 1252)에서 SQL Server 코드 페이지로 변환합니다.
OEM(기본값)	char, varchar 또는 text 데이터 형식의 열을 시스템 OEM 코드 페이지에서 SQL Server 코드 페이지로 변환합니다.
RAW	코드 페이지 간 변환이 일어나지 않습니다. 가장 빠른 옵션입니다.
code_page	데이터 파일의 문자 데이터가 인코딩된 원본 코드 페이지(예: 850)를 나타냅니다. SQL Server 2016 이전의 중요 버전(13.x)은 코드 페이지 65001(UTF-8 인코딩)을 지원하지 않습니다.

FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' }

SQL Server 2017(14.x)부터 이 인수는 RFC 4180 표준을 준수하는 쉼표로 구분된 값 파일을 지정합니다.

SQL Server 2022(16.x)부터 Parquet 및 Delta 형식이 모두 지원됩니다.

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

FORMATFILE = 'format_file_path'

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

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

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

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

FIELDQUOTE = 'field_quote'

SQL Server 2017(14.x)부터 이 인수는 CSV 파일에서 따옴표 문자로 사용되는 문자를 지정합니다. 지정하지 않으면 RFC 4180"정의된 따옴표 문자()가 따옴표 문자로 사용됩니다.

설명

OPENROWSET는 지정된 공급자에 대해 명시적으로 DisallowAdhocAccess 레지스트리 옵션을 0으로 설정하고 Ad Hoc Distributed Queries 고급 구성 옵션을 설정할 때만 OLE DB 데이터 원본에서 원격 데이터에 액세스하는 데 사용할 수 있습니다. 이러한 옵션이 설정되지 않은 경우 기본 동작은 임시 액세스를 허용하지 않습니다.

원격 OLE DB 데이터 원본에 액세스하는 경우 신뢰할 수 있는 연결의 로그인 ID는 클라이언트가 쿼리되는 서버에 연결된 서버에서 자동으로 위임되지 않습니다. 이 경우 인증 위임을 구성해야 합니다.

OLE DB 공급자가 지정된 데이터 원본에서 여러 카탈로그와 스키마를 지원하는 경우에는 카탈로그 이름과 스키마 이름이 필요합니다. OLE DB 공급자가 지원하지 않는 경우 카탈로그 및 스키마의 값을 생략할 수 있습니다. 공급자가 스키마 이름만 지원하는 경우에는 schema.object형식의 두 부분으로 된 이름을 반드시 지정해야 합니다. 공급자가 카탈로그 이름만 지원하는 경우에는catalog.schema.object 형식의 세 부분으로 된 이름을 반드시 지정해야 합니다. SQL Server Native Client OLE DB 공급자를 사용하는 통과 쿼리의 경우 세 부분으로 된 이름을 반드시 지정해야 합니다. 자세한 내용은 Transact-SQL 구문 표기 규칙을 참조하세요.

OPENROWSET 에서는 해당 인수에 대한 변수를 허용하지 않습니다.

OPENDATASOURCE 절에서 OPENQUERY, OPENROWSET 또는 FROM에 대한 모든 호출은 두 호출에 동일한 인수가 제공되는 경우에도 업데이트의 대상으로 사용되는 함수에 대한 호출과는 개별적이고 독립적으로 평가됩니다. 특히 이러한 호출 중 하나의 결과에 적용되는 필터 또는 조인 조건은 다른 호출의 결과에 영향을 미치지 않습니다.

BULK 옵션과 함께 OPENROWSET 사용

다음 Transact-SQL 향상 기능은 이 함수를 OPENROWSET(BULK...) 지원합니다.

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

  OPENROWSET 옵션이 있는 BULK에는 FROM 절에 범위 변수 또는 별칭이라고도 하는 상관 관계 이름이 필요합니다. 열 별칭을 지정할 수 있습니다. 열 별칭 목록을 지정하지 않으면 서식 파일에 열 이름이 있어야 합니다. 열 별칭을 지정하면 서식 파일의 열 이름은 무시됩니다. 다음 예를 참조하십시오.

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

  Important

  AS <table_alias>를 추가하지 않으면 오류가 발생합니다. 메시지 491, 수준 16, 상태 1, 줄 20 FROM 절의 대량 행 집합에 대해 상관관계 이름을 지정해야 합니다.

• SELECT...FROM OPENROWSET(BULK...) 문은 데이터를 테이블로 가져오지 않고 파일의 데이터를 직접 쿼리합니다. SELECT...FROM OPENROWSET(BULK...) 문도 서식 파일을 사용하여 대량 열 별칭을 나열하는 방법으로 열 이름 및 데이터 형식을 지정할 수 있습니다.

• OPENROWSET(BULK...) 또는 INSERT 문에서 MERGE을 원본 테이블로 사용하면 데이터 파일에서 SQL Server 테이블로 데이터를 대량으로 가져올 수 있습니다. 자세한 내용은 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)를 참조하세요. 대량 가져오기로 수행된 행 삽입 작업이 트랜잭션 로그에 기록되는 경우에 대한 자세한 내용은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하세요.

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

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

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

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

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

사용 권한

OPENROWSET 권한은 OLE DB 공급자에게 전달되는 사용자 이름의 사용 권한에 의해 결정됩니다. BULK 옵션을 사용하려면 ADMINISTER BULK OPERATIONS 또는 ADMINISTER DATABASE BULK OPERATIONS 권한이 필요합니다.

예제

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

A. SELECT 및 SQL Server Native Client OLE DB 공급자와 함께 OPENROWSET 사용

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

SQL Server Native Client(약칭 SNAC)는 SQL Server 2022(16.x) 및 SQL Server Management Studio 19(SSMS)에서 제거되었습니다. SQL Server Native Client OLE DB 공급자(SQLNCLI 또는 SQLNCLI11)와 레거시 Microsoft OLE DB Provider for SQL Server(SQLOLEDB)는 모두 새로운 개발에 권장되지 않습니다. 앞으로 SQL Server용 새 Microsoft OLE DB 드라이버(MSOLEDBSQL)로 전환합니다.

다음 예에서는 SQL Server Native Client OLE DB 공급자를 사용하여 HumanResources.Department 원격 서버에서 AdventureWorks2022 데이터베이스의 Seattle1 테이블에 액세스합니다. SQLNCLI를 사용하면 SQL Server가 최신 버전의 SQL Server Native Client OLE DB 공급자로 리디렉션됩니다. SELECT 문은 반환되는 행 집합을 정의하는 데 사용됩니다. 공급자 문자열에는 Server 및 Trusted_Connection 키워드가 포함됩니다. 이러한 키워드는 SQL Server Native Client OLE DB 공급자가 인식합니다.

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT GroupName, Name, DepartmentID
         FROM AdventureWorks2022.HumanResources.Department
         ORDER BY GroupName, Name'
) AS a;

B. Microsoft OLE DB Provider for Jet 사용

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

다음 예에서는 Microsoft OLE DB Provider for Jet을 사용하여 Microsoft Access Customers 데이터베이스의 Northwind 테이블에 액세스합니다.

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

C. INNER JOIN에서 OPENROWSET 및 다른 테이블 사용

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

다음 예에서는 SQL Server Customers 데이터베이스의 로컬 인스턴스에 있는 Northwind 테이블과 해당 컴퓨터에 저장되어 있는 Access Orders 데이터베이스의 Northwind 테이블에서 모든 데이터를 선택합니다.

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

D. 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

E. 서식 파일과 함께 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;

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

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

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

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

G. 형식 파일을 사용하여 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;

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

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

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\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'
);

9\. 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의 데이터에 대한 대량 액세스 예제를 참조하세요.

J. 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 = 'YourStrongPassword1';
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;

k. 외부 원본에 관리 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 (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

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

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

12. 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;

13. 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;

추가 예제

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

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

관련 콘텐츠

• DELETE (Transact-SQL)
• FROM 절과 JOIN, APPLY, PIVOT(Transact-SQL)
• 데이터 대량 가져오기 및 내보내기(SQL Server)
• INSERT(Transact-SQL)
• OPENDATASOURCE(Transact-SQL)
• OPENQUERY(Transact-SQL)
• SELECT(Transact-SQL)
• sp_addlinkedserver(Transact-SQL)
• sp_serveroption(Transact-SQL)
• UPDATE(Transact-SQL)
• WHERE(Transact-SQL)