BULK INSERT 또는 OPENROWSET(BULK...)을 사용하여 SQL Server로 데이터 가져오기

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 Transact-SQL BULK INSERT 문 및 INSERT...SELECT * FROM OPENROWSET(BULK...) 문을 사용하여 데이터 파일의 데이터를 SQL Server 또는 Azure SQL Database 테이블에 대량으로 가져오는 방법을 간략하게 설명합니다. 또한 이 문서에서는 BULK INSERT 및 OPENROWSET(BULK...) 사용과 이러한 메서드를 사용하여 원격 데이터 원본에서 대량 가져오기에 대한 보안 고려 사항도 설명합니다.

참고 항목

BULK INSERT 또는 OPENROWSET(BULK…)을 사용할 경우 SQL Server 버전에서 가장을 처리하는 방법을 이해해야 합니다. 자세한 내용은 이 항목의 후반부에 있는 "보안 고려 사항"을 참조하세요.

BULK INSERT 문

BULK INSERT는 데이터 파일의 데이터를 테이블로 로드합니다. bcp 명령의 in 옵션에서 제공하는 기능과 유사하지만 데이터 파일은 SQL Server 프로세스에서 읽습니다. BULK INSERT 구문에 대한 자세한 내용은 BULK INSERT(Transact-SQL)를 참조하세요.

BULK INSERT 예

OPENROWSET(BULK...) 함수

OPENROWSET 함수를 호출하고 BULK 옵션을 지정하여 OPENROWSET 대량 행 집합 공급자에 액세스합니다. OPENROWSET(BULK...) 함수를 사용하면 OLE DB 공급자를 통해 데이터 파일과 같은 원격 데이터 원본에 연결하여 원격 데이터에 액세스할 수 있습니다.

데이터를 대량으로 가져오려면 INSERT 문 내의 SELECT...FROM 절에서 OPENROWSET(BULK...)를 호출합니다. 데이터를 대량으로 가져오기 위한 기본 구문은 다음과 같습니다.

INSERT ... SELECT * FROM OPENROWSET(BULK...)

INSERT 문에서 사용되는 경우 OPENROWSET(BULK...)은 테이블 힌트를 지원합니다. TABLOCK과 같은 일반적인 테이블 힌트 외에도 BULK 절에는 IGNORE_CONSTRAINTS(CHECK 제약 조건만 무시), IGNORE_TRIGGERS, KEEPDEFAULTS, KEEPIDENTITY 등과 같은 특수 테이블 힌트가 허용됩니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

BULK 옵션의 추가 사용법에 대한 자세한 내용은 OPENROWSET(Transact-SQL)를 참조하세요.

INSERT...SELECT * FROM OPENROWSET(BULK...) 문 - 예

보안 고려 사항

사용자가 SQL Server 로그인을 사용하는 경우 SQL Server 프로세스 계정의 보안 프로필이 사용됩니다. SQL Server 인증을 사용하는 로그인은 데이터베이스 엔진 외부에서 인증될 수 없습니다. 따라서 BULK INSERT 명령이 SQL Server 인증을 사용하는 로그인에 의해 시작되면 데이터에 대한 연결이 SQL Server 프로세스 계정(SQL Server 데이터베이스 엔진 서비스에서 사용하는 계정)의 보안 컨텍스트를 사용하여 설정됩니다.

원본 데이터를 성공적으로 읽으려면 SQL Server 데이터베이스 엔진에서 사용하는 계정에 원본 데이터에 대한 액세스 권한을 부여해야 합니다. 반면 SQL Server 사용자가 Windows 인증을 사용하여 로그온한 경우에는 SQL Server 프로세스의 보안 프로필에 관계없이 해당 사용자 계정으로 액세스할 수 있는 파일만 읽을 수 있습니다.

예를 들어 Windows 인증을 사용하여 SQL Server 인스턴스에 로그인한 사용자를 고려해 보세요. 사용자가 BULK INSERT 또는 OPENROWSET을 사용하여 데이터 파일의 데이터를 SQL Server 테이블로 가져오기 위해서는 사용자 계정에 데이터 파일에 대한 읽기 권한이 필요합니다. 데이터 파일에 액세스할 수 있으므로 SQL Server 프로세스에서 파일에 액세스할 수 있는 권한이 없더라도 사용자가 파일의 데이터를 테이블로 가져올 수 있습니다. 사용자는 SQL Server 프로세스에 파일 액세스 권한을 부여할 필요가 없습니다.

SQL Server 및 Microsoft Windows는 SQL Server 인스턴스가 인증된 Windows 사용자의 자격 증명을 전달하여 다른 SQL Server 인스턴스에 연결할 수 있도록 구성할 수 있습니다. 이러한 작업을 가장 또는 위임이라고 합니다. BULK INSERT 또는 OPENROWSET을 사용할 경우 SQL Server 버전에서 사용자 가장의 보안을 처리하는 방법을 이해해야 합니다. 사용자 가장을 사용하면 데이터 파일이 SQL Server 프로세스 또는 사용자와 다른 컴퓨터에 상주할 수 있습니다. 예를 들어 Computer_A의 사용자에게 Computer_B의 데이터 파일에 대한 액세스 권한이 있고 자격 증명의 위임이 적절하게 설정되어 있는 경우, 해당 사용자는 Computer_C에서 실행 중인 SQL Server 인스턴스에 연결하고 Computer_B의 데이터 파일에 액세스하여 해당 파일의 데이터를 Computer_C의 테이블로 대량 가져올 수 있습니다.

원격 데이터 파일에서 SQL Server로 대량 가져오기

BULK INSERT 또는 INSERT...SELECT * FROM OPENROWSET(BULK...)을 사용하여 다른 컴퓨터에서 데이터를 대량으로 가져오려면 두 컴퓨터 간에 데이터 파일을 공유해야 합니다. 공유 데이터 파일을 지정하려면 \\Servername\Sharename\Path\Filename의 일반 형식으로 해당 UNC(범용 명명 규칙) 이름을 사용합니다. 또한 데이터 파일에 액세스하는 데 사용되는 계정에는 원격 디스크의 파일을 읽는 데 필요한 권한이 있어야 합니다.

예를 들어 다음 BULK INSERT 문은 SalesOrderDetail 라는 데이터 파일의 데이터를 AdventureWorks 데이터베이스의 newdata.txt테이블로 대량 가져옵니다. 이 데이터 파일은 \dailyorders 시스템의 네트워크 공유 디렉터리 salesforce 에서 공유 폴더 computer2에 있습니다.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

참고 항목

클라이언트가 SQL Server와 독립적으로 파일을 읽기 때문에 이 제한은 bcp 유틸리티에 적용되지 않습니다.

Azure Blob Storage에서 대량 가져오기

Azure Blob Storage에서 가져오는 경우 데이터가 퍼블릭(익명 액세스)이 아니면 MASTER KEY로 암호화된 SAS 키를 기준으로 DATABASE SCOPED CREDENTIAL을 만든 다음, BULK INSERT 명령에 사용할 외부 데이터베이스 원본을 만듭니다.

또는 MANAGED IDENTITY를 바탕으로 DATABASE SCOPED CREDENTIAL을 만들어 퍼블릭이 아닌 스토리지 계정의 데이터 액세스 요청을 승인합니다. MANAGED IDENTITY를 사용하는 경우 Azure Storage는 필요한 Azure Blob Storage 컨테이너의 관리 ID에 대한 읽기/쓰기 액세스를 제공하는 Storage Blob 데이터 기여자 기본 제공 Azure RBAC(역할 기반 액세스 제어) 역할을 추가하여 인스턴스의 관리 ID에 대한 권한을 부여해야 합니다. Azure SQL Managed Instance에는 시스템이 할당한 관리 ID가 있으며 하나 이상의 사용자가 할당한 관리 ID가 있을 수도 있습니다. 시스템 할당 관리 ID 또는 사용자 할당 관리 ID를 사용하여 요청에 권한을 부여할 수 있습니다. 권한 부여의 경우 Managed Instance의 default ID가 사용됩니다(기본 사용자 할당 관리 ID 또는 사용자 할당 관리 ID가 지정되지 않은 경우 시스템 할당 관리 ID).

Important

관리 ID는 Azure SQL에만 적용됩니다. SQL Server는 관리 ID를 지원하지 않습니다.

참고 항목

명시적 트랜잭션을 사용하지 마세요. 사용할 경우 4861 오류가 발생합니다.

BULK INSERT 사용

다음 예제에서는 BULK INSERT 명령을 사용하여 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***************';

 -- NOTE: Make sure that you don't have a leading ? in 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/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

다음 예제에서는 BULK INSERT 명령을 사용하여 관리 ID를 통해 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 = 'Managed Identity';

-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://****************.blob.core.windows.net/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');

Important

관리 ID는 Azure SQL에만 적용됩니다. SQL Server는 관리 ID를 지원하지 않습니다.

Azure SQL Database는 Windows 파일에서 읽기를 지원하지 않습니다.

OPENROWSET 사용

다음 예제에서는 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***************';

 -- NOTE: Make sure that you don't have a leading ? in 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/invoices'
          , CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

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;

Important

Azure SQL Database는 Windows 파일에서 읽기를 지원하지 않습니다.

참고 항목