데이터 대량 가져오기 중 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 값 유지

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

이러한 한정자는 다음과 같습니다.

자세한 내용은 bcp 유틸리티, BULK INSERT(Transact-SQL), OPENROWSET BULK(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

예시

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

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;"

bcp를 사용하고 XML이 아닌 서식 파일을 사용하여 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;

BULK INSERT를 사용하고 XML이 아닌 서식 파일을 사용하여 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;

OPENROWSET BULK을 사용하고 XML이 아닌 서식 파일을 사용하여 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;

XML이 아닌 서식 파일을 사용하여 OPENROWSET BULK 및 생성된 ID 값 사용

기본값 및 인수를 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;

관련 작업

• 대량 가져오기 수행 중 Null 또는 기본값 유지(SQL Server)

• 대량 내보내기 또는 가져오기를 위한 데이터 준비

서식 파일을 사용하려면

• bcp를 사용하여 서식 파일 만들기(SQL Server)

• 서식 파일을 사용하여 데이터 대량 가져오기(SQL Server)

• 서식 파일을 사용하여 테이블 열을 데이터 파일 필드에 매핑(SQL Server)

• 서식 파일을 사용하여 데이터 필드 건너뛰기(SQL Server)

• 서식 파일을 사용하여 테이블 열 건너뛰기(SQL Server)

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

• 이전 버전의 SQL Server에서 네이티브 및 문자 형식 데이터 가져오기

• 문자 형식을 사용하여 데이터 가져오기 또는 내보내기(SQL Server)

• 원시 형식을 사용하여 데이터 가져오기 또는 내보내기(SQL Server)

• 유니코드 문자 형식을 사용하여 데이터 가져오기 및 내보내기(SQL Server)

• 데이터를 가져오거나 내보내기 위해 유니코드 네이티브 형식 사용(SQL Server)

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)