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

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

기본적으로 데이터를 테이블로 가져올 때 bcp 명령과 BULK INSERT 문은 테이블의 열에 대해 정의된 모든 기본값을 관찰합니다. 예를 들어 데이터 파일에 null 필드가 있는 경우 열의 기본값이 대신 로드됩니다. bcp 명령 및 BULK INSERT 문을 사용하면 Null 값을 유지하도록 지정할 수 있습니다.

반면, 일반 INSERT 문은 기본값을 삽입하는 대신 null 값을 유지합니다. INSERT ... SELECT * FROM OPENROWSET BULK 문은 일반 INSERT와 동일한 기본 동작을 제공하지만 기본값을 삽입하기 위한 테이블 힌트도 지원합니다.

null 값 유지

다음 한정자는 데이터 파일의 빈 필드가 테이블 열의 기본값(있는 경우)을 상속하는 대신 대량 가져오기 작업 중에 null 값을 유지하도록 지정합니다. OPENROWSET BULK의 경우 기본적으로, 대량 로드 작업에서 지정되지 않은 모든 열은 기본값으로 NULL로 설정됩니다.

명령	한정자	한정자 유형
bcp	-k	스위치
BULK INSERT	KEEPNULLS*	논쟁
INSERT ... SELECT * FROM OPENROWSET(BULK...)	해당 없음	해당 없음

* BULK INSERT(Transact-SQL)의 경우 기본값을 사용할 수 없는 경우 null 값을 허용하도록 테이블 열을 정의해야 합니다.

참고

이러한 한정자는 이러한 대량 가져오기 명령으로 테이블의 DEFAULT 정의 검사를 사용하지 않도록 설정합니다. 동시 INSERT 문의 경우 DEFAULT 정의가 필요합니다.

OPENROWSET BULK에서 SELECT *와 함께 INSERT를 사용하여 기본값 사용

데이터 파일의 빈 필드에 대해 해당 테이블 열이 기본값(있는 경우)을 사용하도록 지정할 수 있습니다. 기본값을 사용하려면 테이블 힌트를 사용합니다.

자세한 내용은 OPENROWSET BULK을 참조하세요.

테스트 조건 예제

이 예제에서는 데이터베이스를 사용하고 이 문서에서 만든 파일의 서식을 지정합니다.

코드 샘플의 로컬 파일 위치를 컴퓨터의 파일 위치로 변경합니다.

샘플 테이블

스크립트는 테스트 데이터베이스와 이름이 지정된 myNulls테이블을 만듭니다. 네 번째 테이블 열 Kids에는 기본값이 있습니다. Microsoft SSMS(SQL Server Management Studio)에서 다음 TRANSACT-SQL을 실행합니다.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myNulls ( 
   PersonID smallint not null,
   FirstName varchar(25),
   LastName varchar(30),
   Kids varchar(13) DEFAULT 'Default Value',
   BirthDate date
   );

샘플 데이터 파일

메모장을 사용하여 빈 파일을 D:\BCP\myNulls.bcp 만들고 다음 샘플 데이터를 삽입합니다. 세 번째 레코드인 네 번째 열에는 값이 없습니다.

1,Anthony,Grosse,Yes,1980-02-23
2,Alica,Fatnowna,No,1963-11-14
3,Stella,Rosenhain,,1992-03-02

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

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

$bcpFile = $dir + 'MyNulls.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 '1,Anthony,Grosse,Yes,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,No,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,,1992-03-02';

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

샘플 비 XML 서식 파일

SQL Server 은(는) 두 유형의 서식 파일, 즉 비 XML 서식 파일과 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 이전 버전의 SQL Server에서 원래 지원했던 서식 파일입니다. 자세한 내용은 비 XML 서식 파일 사용(SQL Server)을 참조하세요.

다음 명령은 bcp 유틸리티를 사용하여 스키마myNulls.fmt에 따라 비 XML 서식 파일을 myNulls생성합니다.

• bcp 명령을 사용하여 서식 파일을 만들려면 데이터 파일 경로 대신 format 인수를 지정하고 nul을 사용합니다.
• 서식 옵션에는 -f 옵션도 필요합니다.
• c 는 문자 데이터를 지정하는 데 사용됩니다.
• t,는 필드 종결자로 쉼표 지정에 사용됩니다.
• T 는 통합 보안을 사용하여 신뢰할 수 있는 연결을 지정하는 데 사용됩니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

중요

XML 형식이 아닌 파일이 캐리지 리턴 및 줄 바꿈으로 끝나는지 확인하세요. 그러지 않으면 다음과 같은 오류 메시지가 표시될 수 있습니다.

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

서식 파일을 만드는 방법에 대한 자세한 내용은 bcp를 사용하여 서식 파일 만들기(SQL Server)를 참조하세요.

대량 가져오기 중에 null을 유지하거나 기본값 사용

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

bcp를 사용하고 서식 파일 없이 null 값 유지

스위치 -k입니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

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

bcp를 사용하고 비 XML 서식 파일로 null 값 유지

-k 및 -f 스위치입니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

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

서식 파일 없이 bcp 및 기본값 사용

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

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

비 XML 서식 파일과 함께 bcp 및 기본값 사용

스위치 -f입니다.

명령 프롬프트에서 다음 명령을 입력합니다.

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

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

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

BULK INSERT를 사용하고 서식 파일 없이 null 값 유지

KEEPNULLS 인수입니다.

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

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

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

BULK INSERT를 사용하고 비 XML 서식 파일로 null 값 유지

KEEPNULLS 및 FORMATFILE 인수.

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

USE TestDatabase;
GO

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

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

BULK INSERT 사용 및 서식 파일 없이 기본값 사용

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

USE TestDatabase;
GO

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

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

XML이 아닌 서식 파일과 함께 BULK INSERT 및 기본값 사용

FORMATFILE 인수입니다.

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

USE TestDatabase;
GO

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

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

OPENROWSET BULK을 사용하고 비 XML 서식 파일로 null 값 유지

FORMATFILE 인수입니다.

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

USE TestDatabase;
GO

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

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

OPENROWSET BULK을 사용하고 비 XML 서식 파일로 기본값 유지

KEEPDEFAULTS 테이블 힌트 및 FORMATFILE 인수입니다.

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

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myNulls;  -- for testing
INSERT INTO dbo.myNulls
WITH (KEEPDEFAULTS) 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myNulls.bcp', 
        FORMATFILE = 'D:\BCP\myNulls.fmt'  
        ) AS t1;

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

관련 작업

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

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

서식 파일을 사용하려면

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

관련 콘텐츠

• BACKUP(Transact-SQL)
• OPENROWSET(Transact-SQL)
• OPENROWSET BULK(Transact-SQL)
• bcp 유틸리티
• BULK INSERT(Transact-SQL)
• 테이블 힌트(Transact-SQL)