OPENROWSET(Transact-SQL)

아티클
12/15/2008

업데이트: 2006년 12월 12일

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

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

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
      , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ]

인수

'provider_name'
레지스트리에 지정된 OLE DB 공급자의 이름(또는 PROGID)을 나타내는 문자열입니다. provider_name에는 기본값이 없습니다.

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

'user_id'
지정한 OLE DB 공급자에게 전달되는 사용자 이름을 나타내는 문자열 상수입니다. user_id는 연결의 보안 컨텍스트를 지정하고 DBPROP_AUTH_USERID 속성으로 전달되어 공급자를 초기화합니다. user_id는 Microsoft Windows 로그인 이름으로 사용할 수 없습니다.

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

'provider_string'
DBPROP_INIT_PROVIDERSTRING 속성으로 전달되어 OLE DB 공급자를 초기화하는 공급자별 연결 문자열입니다. provider_string은 일반적으로 공급자를 초기화하는 데 필요한 모든 연결 정보를 캡슐화합니다. SQL 네이티브 클라이언트 OLE DB 공급자가 인식하는 키워드 목록은 Initialization and Authorization Properties를 참조하십시오.

catalog
지정한 개체가 있는 카탈로그 또는 데이터베이스의 이름입니다.

schema
지정한 개체에 대한 스키마 또는 개체 소유자의 이름입니다.

object
작업할 개체를 고유하게 식별하는 개체 이름입니다.

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

BULK
OPENROWSET의 BULK 행 집합 공급자를 사용하여 파일에서 데이터를 읽습니다. SQL Server 2005에서 OPENROWSET는 대상 테이블에 데이터를 로드하지 않고 데이터 파일에서 읽을 수 있습니다. 이를 통해 간단한 SELECT 문과 함께 OPENROWSET를 사용할 수 있습니다.

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

BULK 옵션을 사용하는 방법은 이 항목의 뒷부분에 나오는 "주의"를 참조하십시오. BULK 옵션에 필요한 사용 권한에 대한 자세한 내용은 이 항목의 뒷부분에 나오는 "사용 권한"을 참조하십시오.

[!참고] OPENROWSET(BULK ...)를 전체 복구 모델로 데이터를 가져오는 데 사용하는 경우 로깅은 최적화되지 않습니다.

대량 가져오기를 위한 데이터 준비 방법은 대량 내보내기 또는 가져오기를 위한 데이터 준비를 참조하십시오.

'data_file'
대상 테이블에 복사할 데이터가 있는 데이터 파일의 전체 경로입니다.

FORMATFILE ='format_file_path'
서식 파일의 전체 경로를 지정합니다. SQL Server 2005에서는 XML 및 비 XML 서식 파일 유형을 지원합니다.

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

서식 파일에 대한 자세한 내용은 서식 파일을 사용하여 데이터를 대량으로 가져오기를 참조하십시오.

< bulk_options >
BULK 옵션에 대한 하나 이상의 인수를 지정합니다.

CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
데이터 파일에서 데이터의 코드 페이지를 지정합니다. CODEPAGE는 문자 값이 127보다 크거나 32보다 작은 char, varchar 또는 text 열이 데이터에 포함된 경우에만 적합합니다.

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 2005 데이터베이스 엔진에서 입력 데이터를 올바르게 해석하려면 코드 페이지가 필요합니다.

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

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

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

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

MAXERRORS **=**maximum_errors
OPENROWSET에서 예외가 발생하기 전에 나타날 수 있는 구문 오류 또는 서식 파일의 정의와 일치하지 않는 행의 최대 수를 지정합니다. MAXERRORS에 도달할 때까지 OPENROWSET는 각각의 잘못된 행을 로드하지 않고 무시하며 잘못된 행을 오류로 간주하여 계산합니다.

maximum_errors의 기본값은 10입니다.

[!참고] MAX_ERRORS는 CHECK 제약 조건이나 money 및 bigint 데이터 형식 변환에 적용되지 않습니다.

ROWS_PER_BATCH **=**rows_per_batch
데이터 파일에 있는 대략적인 데이터 행 수를 지정합니다. 이 값은 실제 행 수와 순서가 같아야 합니다.

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

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

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

중요:
SINGLE_BLOB만 모든 Windows 인코딩 변환을 지원하므로 SINGLE_CLOB 및 SINGLE_NCLOB가 아닌 SINGLE_BLOB 옵션만 사용하여 XML 데이터를 가져오는 것이 좋습니다.

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

SINGLE_NCLOB
data_file을 UNICODE로 읽은 후 현재 데이터베이스의 데이터 정렬을 사용하여 내용을 nvarchar(max) 형식의 단일 행 및 단일 열로 된 행 집합으로 반환합니다.

주의

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

원격 OLE DB 데이터 원본에 액세스할 때 트러스트된 연결의 로그인 ID는 클라이언트가 쿼리 중인 서버에 연결되어 있는 서버에서 자동으로 위임되지 않습니다. 이 경우 인증 위임을 구성해야 합니다. 자세한 내용은 위임용으로 연결된 서버 구성을 참조하십시오.

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

OPENROWSET는 변수를 인수로 받아들이지 않습니다.

BULK 옵션이 있는 OPENROWSET 사용

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

SELECT와 함께 사용된 FROM 절은 전체 SELECT 기능으로 테이블 이름 대신 OPENROWSET(BULK…)를 호출할 수 있습니다.
BULK 옵션이 있는 OPENROWSET에는 FROM 절에 범위 변수 또는 별칭이라고도 하는 상관 관계 이름이 필요합니다. 열 별칭을 지정할 수 있습니다. 열 별칭 목록을 지정하지 않은 경우에는 서식 파일에 반드시 열 이름이 있어야 합니다. 열 별칭을 지정하면 서식 파일의 열 이름은 무시됩니다. 다음 예를 참조하십시오.
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
SELECT…FROM OPENROWSET(BULK...) 문은 데이터를 테이블로 가져오지 않고 파일의 데이터를 직접 쿼리합니다. 또한 SELECT…FROM OPENROWSET(BULK…) 문은 서식 파일을 사용하여 대량 열 별칭을 나열하는 방법으로 열 이름 및 데이터 형식을 지정할 수 있습니다.
INSERT...SELECT * FROM OPENROWSET(BULK...) 문은 데이터 파일에서 SQL Server 테이블로 데이터를 대량으로 가져옵니다. 자세한 내용은 BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기를 참조하십시오.
OPENROWSET BULK 옵션을 INSERT 문과 함께 사용하면 BULK 절에 테이블 힌트를 사용할 수 있습니다. 또한 TABLOCK과 같은 일반적인 테이블 힌트 외에도 BULK 절에는 IGNORE_CONSTRAINTS(CHECK 및 FOREIGN KEY 제약 조건만 무시), IGNORE_TRIGGERS, KEEPDEFAULTS 및 KEEPIDENTITY와 같은 특수 테이블 힌트도 사용할 수 있습니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하십시오.

INSERT...SELECT * FROM OPENROWSET(BULK...) 문을 사용하는 방법은 대량 데이터 가져오기 및 내보내기를 참조하십시오. 대량 가져오기로 수행된 행 삽입 작업이 트랜잭션 로그에 기록되는 경우에 대한 자세한 내용은 대량 가져오기의 최소 로깅을 위한 선행 조건을 참조하십시오.

[!참고] OPENROWSET을 사용할 때는 SQL Server 2005에서 가장을 처리하는 방법을 이해하는 것이 중요합니다. 보안 고려 사항에 대한 자세한 내용은 BULK INSERT 또는 OPENROWSET(BULK...)를 사용하여 데이터 대량 가져오기를 참조하십시오.

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

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

데이터 형식	효과
SQLCHAR 또는 SQLVARYCHAR	데이터를 클라이언트 코드 페이지나 데이터 정렬에 포함된 코드 페이지로 보냅니다.
SQLNCHAR 또는 SQLNVARCHAR	데이터를 유니코드로 보냅니다.
SQLBINARY 또는 SQLVARYBIN	데이터를 변환하지 않고 보냅니다.

사용 권한

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

예

1. SELECT 및 SQL 네이티브 클라이언트 OLE DB 공급자와 함께 OPENROWSET 사용

다음 예에서는 SQL 네이티브 클라이언트 OLE DB 공급자인 SQLNCLI를 사용하여 Seattle1 원격 서버에서 AdventureWorks 데이터베이스의 HumanResources.Department 테이블에 액세스합니다. SELECT 문은 반환되는 행 집합을 정의하는 데 사용됩니다. 공급자 문자열에는 Server 및 Trusted_Connection 키워드가 포함됩니다. 이러한 키워드는 SQL 네이티브 클라이언트 OLE DB 공급자가 인식합니다.

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

2. Microsoft OLE DB Provider for Jet 사용

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

[!참고] 이 예에서는 Access가 설치되어 있다고 가정합니다. 이 예를 실행하려면 Northwind 데이터베이스를 설치해야 합니다. Northwind 데이터베이스 설치 방법은 Northwind 및 pubs 예제 데이터베이스 다운로드를 참조하십시오.

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

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

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

[!참고] 이 예에서는 Access가 설치되어 있다고 가정합니다. 이 예를 실행하려면 Northwind 데이터베이스를 설치해야 합니다. Northwind 데이터베이스 설치 방법은 Northwind 및 pubs 예제 데이터베이스 다운로드를 참조하십시오.

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 
GO

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

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

USE AdventureWorks
GO
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

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

다음 예에서는 서식 파일을 사용하여 다음 데이터가 들어 있는 탭으로 분리된 텍스트 파일인 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;

추가 예

INSERT...SELECT * FROM OPENROWSET(BULK...) 사용에 대한 추가 예는 다음 항목을 참조하십시오.

참고 항목

참조

DELETE(Transact-SQL)
FROM(Transact-SQL)
INSERT(Transact-SQL)
OPENDATASOURCE(Transact-SQL)
OPENQUERY(Transact-SQL)
행 집합 함수(Transact-SQL)
SELECT(Transact-SQL)
sp_addlinkedserver(Transact-SQL)
sp_serveroption(Transact-SQL)
UPDATE(Transact-SQL)
WHERE(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스	내역
2006년 12월 12일	새로운 내용 IGNORE_CONSTRAINTS 테이블 힌트가 CHECK 및 FOREIGN KEY 제약 조건을 모두 무시한다는 정보를 추가했습니다.
2006년 7월 17일	새로운 내용 OLE DB 데이터 원본을 자주 참조하려면 OPENROWSET 대신 연결된 서버를 사용해야 한다는 내용을 추가했습니다. "주의" 섹션에서 다음 내용을 변경했습니다. 이중 홉을 통해 원격 OLE DB 데이터 원본에 액세스할 경우 인증 위임을 구성해야 한다는 내용을 추가했습니다. SQL 네이티브 클라이언트 OLE DB 공급자를 사용하는 통과 쿼리에 세 부분으로 된 이름을 지정한다는 내용을 추가했습니다. BULK 옵션 사용에 대한 자세한 내용을 추가했습니다.

2006년 12월 12일

새로운 내용

IGNORE_CONSTRAINTS 테이블 힌트가 CHECK 및 FOREIGN KEY 제약 조건을 모두 무시한다는 정보를 추가했습니다.

2006년 7월 17일