다음을 통해 공유

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

  • 아티클

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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 스키마에 따라 비 xml 서식 파일(myIdentity.fmt)을 생성합니다. 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)