데이터 대량 가져오기 중 ID 값 유지(SQL Server)

  • 아티클

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

Microsoft SQL Server 인스턴스로 ID 값이 들어 있는 데이터 파일을 대량으로 가져옵니다. 기본적으로 가져온 데이터 파일의 ID 열 값은 무시되고 SQL Server가 자동으로 고유 값을 할당합니다. 고유 값은 테이블을 만드는 동안 지정된 시드 및 증분 값을 기반으로 합니다.

데이터 파일에 테이블이의 ID 열 값이 포함되지 않은 경우 서식 파일을 사용하여 데이터를 가져올 때 테이블의 ID 열을 생략하도록 지정합니다. 자세한 내용은 서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)를 참조하세요.

개요
ID 값 유지
예제 테스트 조건
 ● 샘플 테이블
 ● 샘플 데이터 파일
 ● 샘플 비 XML 서식 파일
예제
 ● 서식 파일 없이 bcp 사용 및 ID 값 유지
 ● 비 XML 서식 파일과 함께 bcp 사용 및 ID 값 유지
 ● 서식 파일 없이 bcp 및 생성된 ID 값 사용
 ● 비 XML 서식 파일과 함께 bcp 및 생성된 ID 값 사용
 ● 서식 파일 없이 BULK INSERT 사용 및 ID 값 유지
 ● 비 XML 서식 파일과 함께 BULK INSERT 사용 및 ID 값 유지
 ● 서식 파일 없이 BULK INSERT 및 생성된 ID 값 사용
 ● 비 XML 서식 파일과 함께 BULK INSERT 및 생성된 ID 값 사용
 ● 비 XML 서식 파일과 함께 OPENROWSET 사용 및 ID 값 유지
 비 XML 서식 파일과 함께 OPENROWSET 및 생성된 ID 값 사용

ID 값 유지

테이블로 데이터 행을 대량으로 가져오는 동안 SQL Server가 ID 값을 할당하지 못하도록 하려면 적절한 keep-identity 명령 한정자를 사용합니다. keep-identity 한정자를 지정하면 SQL Server는 데이터 파일의 ID 값을 사용합니다. 이러한 한정자는 다음과 같습니다.

명령 ID 유지 한정자 한정자 유형
bcp E- 스위치
BULK INSERT KEEPIDENTITY 인수
INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY 테이블 힌트

자세한 내용은 bcp 유틸리티, BULK INSERT(Transact-SQL), OPENROWSET(Transact-SQL), INSERT(Transact-SQL), SELECT(Transact-SQL), 테이블 힌트(Transact-SQL)를 참조하세요.

참고 항목

여러 테이블에서 사용할 수 있거나 테이블을 참조하지 않고 애플리케이션에서 호출할 수 있는 자동으로 증가하는 번호를 만들려면 시퀀스 번호를 참조하세요.

예제 테스트 조건

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

샘플 테이블

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

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

샘플 데이터 파일

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

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

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

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.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 '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

샘플 비 XML 서식 파일

SQL Server 은(는) 두 유형의 서식 파일, 즉 비 XML 서식 파일과 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 이전 버전의 SQL Server에서 원래 지원했던 서식 파일입니다. 자세한 내용은 비 XML 서식 파일(SQL Server)을 검토하세요. 다음 명령은 bcp 유틸리티를 사용하여 myIdentity.fmt 스키마에 따라 비 XML 서식 파일 myIdentity을 생성합니다. bcp 명령을 사용하여 서식 파일을 만들려면 데이터 파일 경로 대신 format 인수를 지정하고 NUL 을 사용합니다. 서식 옵션에는 -f 옵션도 필요합니다. 또한 이 예제에서는 한정자 c는 문자 데이터를 지정하는 데 사용하고, t는 쉼표를 필드 종결자로 지정하는 데 사용하며 T는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용합니다. 명령 프롬프트에서 다음 명령을 입력합니다.

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

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

Important

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

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

예

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

서식 파일 없이 bcp 사용 및 ID 값 유지

-E 스위치. 명령 프롬프트에서 다음 명령을 입력합니다.

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

비 XML 서식 파일과 함께 bcp 사용 및 ID 값 유지

-E 및 -f 스위치. 명령 프롬프트에서 다음 명령을 입력합니다.

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

서식 파일 없이 bcp 및 생성된 ID 값 사용

기본값을 사용합니다. 명령 프롬프트에서 다음 명령을 입력합니다.

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

비 XML 서식 파일과 함께 bcp 및 생성된 ID 값 사용

기본값 및 -f 스위치를 사용합니다. 명령 프롬프트에서 다음 명령을 입력합니다.

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

서식 파일 없이 BULK INSERT 사용 및 ID 값 유지

KEEPIDENTITY 인수. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
	FROM 'D:\BCP\myIdentity.bcp'
	WITH (
		DATAFILETYPE = 'char',  
		FIELDTERMINATOR = ',',  
		KEEPIDENTITY
		);

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

비 XML 서식 파일과 함께 BULK INSERT 사용 및 ID 값 유지

KEEPIDENTITY 및 FORMATFILE 인수. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myIdentity.fmt',
		KEEPIDENTITY
		);

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

서식 파일 없이 BULK INSERT 및 생성된 ID 값 사용

기본값을 사용합니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
	  );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

비 XML 서식 파일과 함께 BULK INSERT 및 생성된 ID 값 사용

기본값 및 FORMATFILE 인수를 사용합니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
		FORMATFILE = 'D:\BCP\myIdentity.fmt'
		);

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

비 XML 서식 파일과 함께 OPENROWSET(BULK...) 사용 및 ID 값 유지

KEEPIDENTITY 테이블 힌트 및 FORMATFILE 인수. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
	SELECT *
	FROM OPENROWSET (
		BULK 'D:\BCP\myIdentity.bcp', 
		FORMATFILE = 'D:\BCP\myIdentity.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Using OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

기본값 및 FORMATFILE 인수를 사용합니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
	SELECT FirstName, LastName, BirthDate
	FROM OPENROWSET (
		BULK 'D:\BCP\myIdentity.bcp', 
		FORMATFILE = 'D:\BCP\myIdentity.fmt'  
		) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

관련 작업

서식 파일을 사용하려면

대량 가져오기 또는 대량 내보내기를 위한 데이터 형식을 사용하려면

bcp를 사용하여 데이터 형식을 호환 가능하도록 지정하려면

  1. 필드 및 행 종결자 지정(SQL Server)

  2. bcp를 사용하여 데이터 파일에 접두사 길이 지정(SQL Server)

  3. bcp를 사용하여 파일 스토리지 유형 지정(SQL Server)

참고 항목

BACKUP(Transact-SQL)
bcp 유틸리티
BULK INSERT(Transact-SQL)
OPENROWSET(Transact-SQL)
테이블 힌트(Transact-SQL)
데이터를 가져오거나 내보내기 위한 서식 파일(SQL Server)