학습
모듈
Azure SQL Database의 데이터 조작 옵션 살펴보기 - Training
Azure SQL Database에서 REST 엔드포인트를 호출하고 Azure Functions를 사용하여 데이터를 조작하는 방법을 알아봅니다. 또한 Azure SQL Database에서 데이터를 가져오거나 내보내는 데 사용되는 다양한 도구와 옵션도 살펴봅니다.
적용 대상: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System(PDW)
기본적으로 데이터를 테이블로 가져올 때 bcp 명령과 BULK INSERT 문은 테이블의 열에 대해 정의된 모든 기본값을 관찰합니다. 예를 들어 데이터 파일에 null 필드가 있는 경우 열의 기본값이 대신 로드됩니다. bcp 명령 및 BULK INSERT 문을 사용하면 Null 값을 유지하도록 지정할 수 있습니다.
반대로 일반 INSERT 문은 기본값을 삽입하는 대신 Null 값을 유지합니다. INSERT ... SELECT * FROM OPENROWSET(BULK...) 문은 일반 INSERT와 같은 기본 동작을 제공하지만 기본값 삽입에 대한 테이블 힌트를 추가로 지원합니다.
다음 한정자는 데이터 파일의 빈 필드가 테이블 열의 기본값(있는 경우)을 상속하는 대신 대량 가져오기 작업 중에 null 값을 유지하도록 지정합니다. OPENROWSET의 경우 기본적으로 대량 로드 작업에 지정되지 않은 열은 NULL로 설정됩니다.
명령 | 한정자 | 한정자 유형 |
---|---|---|
bcp | -k | 스위치 |
BULK INSERT | KEEPNULLS* | 인수 |
INSERT ... SELECT * FROM OPENROWSET(BULK...) | 해당 없음 | 해당 없음 |
* BULK INSERT의 경우 기본값을 사용할 수 없으면 Null 값을 허용하도록 테이블 열을 정의해야 합니다.
참고
이러한 한정자는 대량 가져오기 명령을 통해 테이블에서 DEFAULT 정의 확인을 비활성화합니다. 그러나 동시 INSERT 문의 경우 DEFAULT 정의가 필요합니다.
데이터 파일의 빈 필드에 대해 해당 테이블 열이 기본값(있는 경우)을 사용하도록 지정할 수 있습니다. 기본값을 사용하려면 테이블 힌트 KEEPDEFAULTS를 사용합니다.
참고
자세한 내용은 INSERT (Transact-SQL), SELECT(Transact-SQL), OPENROWSET(Transact-SQL), 테이블 힌트(Transact-SQL)를 참조하세요.
이 항목의 예제는 아래에 정의된 테이블, 데이터 파일 및 서식 파일을 기반으로 합니다.
아래 스크립트는 테스트 데이터베이스와 이름이 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;
SQL Server 은(는) 두 유형의 서식 파일, 즉 비 XML 서식 파일과 XML 서식 파일을 지원합니다. 비 XML 서식 파일은 이전 버전의 SQL Server에서 원래 지원했던 서식 파일입니다. 자세한 내용은 비 XML 서식 파일(SQL Server)을 검토하세요. 다음 명령에서는 bcp 유틸리티를 사용하여 myNulls
스키마에 따라 비 xml 서식 파일(myNulls.fmt
)을 생성합니다. 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
서식 파일을 만드는 방법은 서식 파일 만들기(SQL Server)를 사용하세요.
아래 예제에서는 위에서 만든 데이터베이스, 데이터 파일 및 서식 파일을 사용합니다.
-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;"
-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;"
명령 프롬프트에서 다음 명령을 입력합니다.
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;"
-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;"
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;
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;
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;
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;
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;
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;
서식 파일을 사용하려면
대량 가져오기 또는 대량 내보내기를 위한 데이터 형식을 사용하려면
bcp를 사용하여 데이터 형식을 호환 가능하도록 지정하려면
BACKUP(Transact-SQL)
OPENROWSET(Transact-SQL)
bcp 유틸리티
BULK INSERT(Transact-SQL)
테이블 힌트(Transact-SQL)
학습
모듈
Azure SQL Database의 데이터 조작 옵션 살펴보기 - Training
Azure SQL Database에서 REST 엔드포인트를 호출하고 Azure Functions를 사용하여 데이터를 조작하는 방법을 알아봅니다. 또한 Azure SQL Database에서 데이터를 가져오거나 내보내는 데 사용되는 다양한 도구와 옵션도 살펴봅니다.