EXCEL 엑셀 버튼으로 MSSQL 데이터베이스에 데이터 적재하기 (VBA + Stored Procedure) 엑셀고수

EXCEL 엑셀 버튼으로 MSSQL 데이터베이스에 데이터 적재하기 (VBA + Stored Procedure)  엑셀고수

안녕하세요! 엑셀의 특정 범위 데이터를 버튼 클릭 한 번으로 MSSQL 데이터베이스 테이블에 저장하는 방법을 쉽고 자세하게, 순서대로 설명해 드리겠습니다.

아래 단계를 차근차근 따라오시면 됩니다.

---

전체 과정 요약

1. 데이터베이스 준비 (MSSQL): 데이터를 저장할 테이블과, 데이터를 삽입할 프로시저(Stored Procedure)를 만듭니다.
2. 엑셀 파일 준비: 데이터를 입력하고, 매크로를 실행할 버튼을 만듭니다.
3. VBA 코드 작성: 엑셀과 데이터베이스를 연결하고, 버튼 클릭 시 데이터를 전송하는 코드를 작성합니다.

---

1단계: 데이터베이스 준비 (in SSMS)

먼저 SQL Server Management Studio(SSMS)를 열어 데이터베이스와 테이블, 프로시저를 생성합니다.

1. 테이블 생성

엑셀 데이터를 저장할 테이블을 만듭니다. 여기서는 제품명, 수량, 가격, 그리고 데이터가 언제 입력되었는지 확인할 입력일시 컬럼을 갖는 테이블을 예시로 만들겠습니다.

Generated sql

-- 사용할 데이터베이스를 선택합니다. (예: MyDatabase)
USE MyDatabase;
GO

-- 만약 기존에 같은 이름의 테이블이 있다면 삭제합니다.
IF OBJECT_ID('dbo.ExcelData', 'U') IS NOT NULL
    DROP TABLE dbo.ExcelData;
GO

-- 데이터를 저장할 테이블을 생성합니다.
CREATE TABLE dbo.ExcelData (
    ID INT IDENTITY(1,1) PRIMARY KEY, -- 자동 증가하는 고유 ID
    ProductName NVARCHAR(100),         -- 제품명 (엑셀 A열)
    Quantity INT,                      -- 수량 (엑셀 B열)
    Price DECIMAL(18, 2),              -- 가격 (엑셀 C열)
    ImportDate DATETIME DEFAULT GETDATE() -- 데이터 입력 시각 (자동으로 현재 시간 기록)
);
GO

 

Use code with caution.SQL

2. 저장 프로시저(Stored Procedure) 생성

VBA에서 이 프로시저를 호출하여 데이터를 한 줄씩 테이블에 INSERT하게 됩니다. 프로시저를 사용하면 보안에 유리하고 관리가 편합니다.

프로시저 설명:

• @p_ProductName, @p_Quantity, @p_Price 라는 3개의 파라미터를 받습니다.
• 받아온 파라미터 값들을 ExcelData 테이블에 삽입(INSERT)합니다.

Generated sql

-- 사용할 데이터베이스를 선택합니다.
USE MyDatabase;
GO

-- 만약 기존에 같은 이름의 프로시저가 있다면 삭제합니다.
IF OBJECT_ID('dbo.sp_InsertExcelData', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_InsertExcelData;
GO

-- 엑셀 데이터를 삽입하는 저장 프로시저를 생성합니다.
CREATE PROCEDURE dbo.sp_InsertExcelData
    @p_ProductName NVARCHAR(100),
    @p_Quantity INT,
    @p_Price DECIMAL(18, 2)
AS
BEGIN
    INSERT INTO dbo.ExcelData (ProductName, Quantity, Price)
    VALUES (@p_ProductName, @p_Quantity, @p_Price);
END;
GO

 

Use code with caution.SQL

(선택 사항) 테이블 초기화 프로시저: 데이터를 새로 넣기 전에 기존 데이터를 모두 지우고 싶을 경우를 대비해, 테이블을 비우는 프로시저를 만들어두면 편리합니다.

Generated sql

IF OBJECT_ID('dbo.sp_ClearExcelData', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_ClearExcelData;
GO
CREATE PROCEDURE dbo.sp_ClearExcelData
AS
BEGIN
    TRUNCATE TABLE dbo.ExcelData;
END;
GO

 

Use code with caution.SQL

---

2단계: 엑셀 파일 준비

이제 엑셀 파일을 설정할 차례입니다.

1. 데이터 입력 및 파일 저장

• 아래와 같이 1행에는 헤더(제목)를, 2행부터는 실제 데이터를 입력합니다.
• 파일을 저장할 때, Excel 매크로 사용 통합 문서 (*.xlsm) 형식으로 저장해야 합니다.

	A	B	C
1	제품명	수량	가격
2	노트북	10	1200000
3	모니터	25	350000
4	키보드	100	45000

2. 개발 도구 탭 활성화

VBA를 사용하려면 '개발 도구' 탭이 필요합니다. 만약 보이지 않는다면,
파일 > 옵션 > 리본 사용자 지정 > 오른쪽 목록에서 개발 도구 체크 > 확인

3. 버튼 생성

• 개발 도구 탭 > 삽입 > 양식 컨트롤에서 단추(버튼)를 선택합니다.
• 시트의 적절한 위치에 드래그하여 버튼을 그립니다.
• 버튼을 그리면 매크로 지정 창이 자동으로 뜹니다. 새로 만들기를 클릭합니다.

새로 만들기를 클릭하면, VBA 편집기(VBE)가 열리면서 코드를 작성할 수 있는 공간이 나타납니다.

---

3단계: VBA 코드 작성

이제 가장 중요한 VBA 코드를 작성할 차례입니다.

1. ADO 라이브러리 참조 추가

VBA에서 데이터베이스에 연결하려면 ADO(ActiveX Data Objects) 라이브러리가 필요합니다.

• VBA 편집기(방금 열린 창)에서 도구(T) > 참조(R)를 클릭합니다.
• 목록에서 **Microsoft ActiveX Data Objects x.x Library**를 찾아 체크합니다. (여러 버전이 있다면 가장 높은 숫자를 선택하세요.)
• 확인을 누릅니다.

2. VBA 코드 붙여넣기 및 수정

새로 만들기를 눌렀을 때 자동으로 생성된 Sub 단추1_Click() ... End Sub 사이에 아래 코드를 복사해서 붙여넣으세요.

Generated vb

Sub 단추1_Click()

    ' 1. 기본 변수 설정
    Dim conn As Object 'ADODB.Connection
    Dim cmd As Object  'ADODB.Command
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' 에러 발생 시 ErrorHandler로 이동
    On Error GoTo ErrorHandler

    ' 2. 연결 문자열(Connection String) 설정 *** 가장 중요! 사용자 환경에 맞게 수정 ***
    Dim connStr As String
    connStr = "Provider=SQLNCLI11;" & _
              "Server=여기에_서버_주소_입력;" & _
              "Database=MyDatabase;" & _
              "Trusted_Connection=yes;" ' Windows 인증 사용 시
    ' -- 만약 SQL Server 인증을 사용한다면 아래와 같이 수정 --
    ' connStr = "Provider=SQLNCLI11;" & _
    '           "Server=여기에_서버_주소_입력;" & _
    '           "Database=MyDatabase;" & _
    '           "Uid=여기에_SQL_사용자ID;" & _
    '           "Pwd=여기에_SQL_비밀번호;"

    ' 3. 데이터베이스 연결 객체 생성 및 연결
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connStr

    ' 4. (선택 사항) 기존 데이터 삭제 프로시저 실행
    '    새 데이터를 넣기 전에 테이블을 비우고 싶을 경우 주석 해제
    ' conn.Execute "EXEC sp_ClearExcelData"

    ' 5. 엑셀 데이터 범위 설정
    Set ws = ThisWorkbook.Sheets("Sheet1") '데이터가 있는 시트 이름
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'A열 기준으로 마지막 데이터 행 찾기

    ' 6. 데이터 행을 순회하며 DB에 적재 (2행부터 시작)
    For i = 2 To lastRow
        ' Command 객체 생성
        Set cmd = CreateObject("ADODB.Command")
        Set cmd.ActiveConnection = conn
        cmd.CommandText = "sp_InsertExcelData" '실행할 프로시저 이름
        cmd.CommandType = 4 'adCmdStoredProc (저장 프로시저 타입)

        ' 프로시저에 파라미터 전달
        cmd.Parameters.Append cmd.CreateParameter("@p_ProductName", 202, 1, 100, ws.Cells(i, 1).Value) 'A열 값
        cmd.Parameters.Append cmd.CreateParameter("@p_Quantity", 3, 1, , ws.Cells(i, 2).Value)     'B열 값
        cmd.Parameters.Append cmd.CreateParameter("@p_Price", 6, 1, , ws.Cells(i, 3).Value)       'C열 값

        ' 프로시저 실행
        cmd.Execute
    Next i

    ' 7. 자원 해제 및 성공 메시지
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
    MsgBox "총 " & (lastRow - 1) & "개의 데이터가 성공적으로 적재되었습니다.", vbInformation, "작업 완료"

    Exit Sub ' 정상 종료

' 8. 에러 처리
ErrorHandler:
    MsgBox "오류가 발생했습니다." & vbCrLf & vbCrLf & Err.Description, vbCritical, "오류"
    ' 만약 연결이 열려있다면 닫기
    If Not conn Is Nothing Then
        If conn.State = 1 Then conn.Close
    End If
    Set cmd = Nothing
    Set conn = Nothing

End Sub

 

Use code with caution.Vb

코드 수정 포인트 (가장 중요!)

위 코드에서 ' 2. 연결 문자열(Connection String) 설정 부분을 본인의 환경에 맞게 반드시 수정해야 합니다.

• Server=여기에_서버_주소_입력: 본인의 MSSQL 서버 주소(IP 또는 서버 이름)를 입력합니다. (예: 192.168.0.100 또는 MY-PC\SQLEXPRESS)
• Database=MyDatabase: 위에서 테이블을 만든 데이터베이스 이름을 입력합니다.
• 인증 방식 선택 (둘 중 하나만 사용)
  • Windows 인증: 현재 윈도우 로그인 계정으로 SQL 서버에 접속할 수 있다면 Trusted_Connection=yes;를 사용합니다. (별도 수정 필요 없음)
  • SQL Server 인증: SQL Server에 별도의 아이디와 비밀번호로 접속한다면 Trusted_Connection 라인을 지우고, Uid와 Pwd가 있는 라인의 주석을 해제한 뒤 아이디와 비밀번호를 입력합니다.

---

4단계: 실행 및 확인

1. VBA 편집기 창을 닫고 엑셀 시트로 돌아옵니다.
2. 만들어 둔 버튼을 클릭합니다.
3. "작업 완료" 메시지 창이 뜨면 성공입니다.
4. SSMS에서 아래 쿼리를 실행하여 데이터가 잘 들어갔는지 확인합니다.

Generated sql

SELECT * FROM dbo.ExcelData;

 

Use code with caution.SQL

이제 엑셀에 새로운 데이터를 추가하고 버튼을 누를 때마다 해당 데이터가 데이터베이스에 차곡차곡 쌓이게 됩니다.