MSSQL 데이터 가져오기, 내보내기(BCP, xp_cmdshell, BULK INSERT)

1. xp_cmdshell이란 MSSQL에서 운영체제 명령을 실행할 수 있도록하는 함수입니다.

 

다음쿼리를 이용해서 xp_cmdshell이 활성화 되어 있는지 확인할 수 있습니다.

SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell'

 

만약, xp_cmdshell이 비활성화되어 있다면 다음과 같은 쿼리를 이용해서 활성화시킬 수 있습니다.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

 

2. BCP : 대량 복사 프로그램 유틸리티(bulk copy program utility, bcp) : bcp 유틸리티를 사용하여 많은 수의 새 행을 SQL Server 테이블로 가져오거나 테이블에서 데이터 파일로 데이터를 내보낼 수 있습니다.

DECLARE @SQL         AS VARCHAR(255)
DECLARE @TSQL        AS VARCHAR(255)
DECLARE @FileName    AS VARCHAR(50) = 'NAME.txt'
DECLARE @result      AS INT

SET @SQL = 'SELECT CUST_NM FROM whk_edu.dbo.TBL_CST WITH(NOLOCK) WHERE 1=1 '
SET @TSQL = 'BCP "' + @SQL + '" QUERYOUT "1:\1111\1111\' + @FileName + '" -b 1000 -c -T'

EXEC @result = xp_cmdshell @TSQL
IF (@result = 0)  
    PRINT 'Success'  
ELSE  
    PRINT 'Failure'

• queryout : 쿼리에서 복사하며 쿼리에서 데이터를 대량 복사하는 경우에만 지정해야 합니다.
• -b : 가져온 데이터의 일괄 처리당 행 수를 지정
• -c : 문자 데이터 형식을 사용하여 작업을 수행
• -r"\n" : 행 종결자를 지정합니다. 기본값은 \n (줄 바꿈 문자)입니다.
• -t"\t" : 필드 종결자를 지정합니다. 기본값은 \t (탭 문자)입니다.
• -T : bcp 유틸리티가 통합 보안을 사용하는 트러스트된 연결을 통해 SQL Server 로 연결되도록 지정합니다. 네트워크 사용자의 보안 자격 증명, login_id및 password 는 필요하지 않습니다. -T 를 지정하지 않은 경우 성공적으로 로그인하려면 -U 와 -P 를 지정해야 합니다.

 

---

 

3. BULK INSERT : 데이터 파일을 SQL Server에서 사용자가 지정한 형식으로 데이터베이스 테이블이나 뷰로 가져옵니다.

DECLARE @SQL              AS VARCHAR(255)
DECLARE @TableName        AS VARCHAR(50) = 'TBL_CST'
DECLARE @LocalFilePath    AS VARCHAR(255) = '1:\1111\1111\NAME.txt'

SET @SQL = 'BULK INSERT whk_edu.dbo.' + @TableName + ' FROM ''' + @LocalFilePath + ''' '
SET @SQL += 'WITH(FIRSTROW = 1, KEEPIDENTITY, DATAFILETYPE = ''CHAR'', FIELDTERMINATOR = '';'', ROWTERMINATOR = '''+CHAR(10)+''')'

EXEC (@SQL)

• DATAFILETYPE : char(기본값) 문자 형식
• FIRSTROW  : 로드할 첫 번째 행의 번호를 지정
• KEEPIDENTITY : 가져온 데이터 파일의 ID 값이 ID 열에 사용되도록 지정
• FIELDTERMINATOR : char 및 widechar 데이터 파일에 사용할 필드 종결자를 지정
• ROWTERMINATOR : char 및 widechar 데이터 파일에 사용할 행 종결자를 지정

 

 

---

참고

xp_cmdshell(Transact-SQL) - SQL Server

bcp 유틸리티 - SQL Server

BULK INSERT(Transact-SQL) - SQL Server

xp_cmdshell 활성화 쿼리