[MSSQL]쿼리를 이용하여 엑셀(EXCEL) 파일로 바로 저장하기

-- TXT 파일 저장하는 방식과 유사 합니다.

-- 참조 부탁드립니다.

 

SET NOCOUNT ON

--기본셋팅 : 이부분은 대도록이면 수정 하지 맙시다...ㅎㅎㅎ

DECLARE @Conn int    -- ADO Connection object to create XLS

             , @hr int     -- OLE return value

             , @src varchar(255)   -- OLE Error Source

             , @desc varchar(255)   -- OLE Error Description

             , @Path varchar(255)   -- Drive or UNC path for XLS

             , @Connect varchar(255)   -- OLE DB Connection string for Jet 4 Excel ISAM

             , @WKS_Created bit   -- Whether the XLS Worksheet exists

             , @TABLE varchar(128)   -- Name of the XLS Worksheet (table)

             , @ServerName nvarchar(128)  -- Linked Server name for XLS

             , @DDL varchar(8000)   -- Jet4 DDL for the XLS WKS table creation

             , @SQL varchar(8000)   -- INSERT INTO XLS T-SQL

             , @COL1 varchar(1000)  -- Row1

             , @COL2 varchar(1000)  -- Row2

--기본설정

--------------------------------------------------------------------------

--패치

DECLARE C_INSA CURSOR 

FOR

   -- 쿼서를 생성 합니다.

   SELECT SUBSTRING(USERNAMEENG,1,1)

      FROM DTS_DB.DBO.USER_TB

    WHERE SUBSTRING(USERNAMEENG,1,1) IN ('A','B','C')

    GROUP BY SUBSTRING(USERNAMEENG,1,1)

    ORDER BY SUBSTRING(USERNAMEENG,1,1)

 

OPEN C_INSA                                                          -- 쿼서 오픈

DECLARE @V_INSA AS VARCHAR(10)

 

--첫 로우 FETCH

FETCH NEXT FROM C_INSA INTO @V_INSA

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

   -- 엑셀 파일이 저장될 디렉토리

   -- 3개의 엑셀 파일이 생성됨( A, B, C)

   SET @Path = 'D:\TEMP\INSA_'+@V_INSA+'.xls'

   -- 엑셀 파일을 상요하기 위한 선언

   SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'

 

   --테이블명 & db명

   SET @TABLE = 'TEAM_CODE'                        -- 테이블 = 시트명 이라고 생각 하시면 됩니다.

   SET @ServerName = 'INSA_TO_EXCEL'            

 

   --컬럼

   SET @COL1 = 'USERID, USERNAME,USERNAMECHN,USERNAMEENG'

   -- 저장할 컬럼 형을 선언합니다...주로 (Text로 걍 선언하지요...ㅎㅎㅎ) 

   SET @COL2 = 'USERID Text, USERNAME Text, USERNAMECHN Text, USERNAMEENG Text'

 

   -- 엑셀로 인서트 쿼리

   -- 생성된 DB의 TABLE에 데이터를 저장한다.

   SET @SQL = 'INSERT INTO '+@ServerName+'...'+@TABLE+' ('+@COL1+') '

   SET @SQL = @SQL+'SELECT '+@COL1+' FROM  DTS_DB.DBO.USER_TB'

   SET @SQL = @SQL + ' WHERE SUBSTRING(USERNAMEENG,1,1) = ''' + @V_INSA + ''''

 

   -- 테이블 생성

   SET @DDL = 'CREATE TABLE '+@TABLE+' ('+@COL2+')'

 

   --커넥션

   -- sp_OACreate : Microsoft?? SQL Server™의 인스턴스에서 OLE개체의 인스턴스를 만듭니다.

   -- sp_OAGetErrorInfo : OLE 자동화 오류 정보를 얻습니다.

   -- sp_OAMethod : OLE 개체의 메서드를 호출합니다.

   -- MS-SQL 오라인 설명서를 참조하시면 자세한 정보가 있습니다.

   EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT

   EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect

   EXEC @hr = sp_OAMethod @Conn, 'Open'

   EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 

 

   -- 엑셀 표 형태로 변환

   IF @hr = 0x80040E14 

      OR @hr = 0x80042732

   BEGIN

      IF @hr = 0x80040E14

      BEGIN

         -- 오류가 발생하면 실행 된다.

         PRINT char(9)+''''+@TABLE+''' Worksheet exists for append'

         SET @WKS_Created = 0

      END

  

      SET @hr = 0 -- ignore these errors

   END

 

   -- sp_OADestroy : 만들어진 OLE 개체를 삭제합니다.

   EXEC @hr = sp_OADestroy @Conn

 

   --엑셀로 inst

   -- sp_addlinkedserver : OLE DB 데이터 원본과 유형이 다른 분산 쿼리를 액세스할 수 있도록 해 주는 연결된 서버를

   --                               작성합니다. sp_addlinkedserver와 연결된 서버를 작성한 다음에는 이 서버가 분산 쿼리를

   --                               실행할 수 있습니다. 연결된 서버가 Microsoft?? SQL Server™로 정의된 경우에는 원격 저장

   --                               프로시저를 실행할 수 있습니다.

   EXEC sp_addlinkedserver @server = @ServerName

                                      , @srvproduct = 'Microsoft Excel Workbook'

                                      , @provider = 'Microsoft.Jet.OLEDB.4.0'

                                      , @datasrc = @Path

                                      , @provstr = 'Excel 8.0'

   -- sp_addlinkedsrvlogin : Microsoft?? SQL Server™ 로컬 인스턴스의 로그인과 연결된 서버의 원격 로그인 간의

   --                                  매핑을 작성하거나 업데이트합니다.

   EXEC sp_addlinkedsrvlogin @ServerName, 'false' 

 

   EXEC (@SQL)

 

   -- sp_dropserver : 로컬 Microsoft?? SQL Server™의 연결된 서버 및 원격 서버에 알려진 목록에서 서버를 제거합니다.

   EXEC sp_dropserver @ServerName, 'droplogins'

 

--다음 로우 FEETCH - 루프

FETCH NEXT FROM C_INSA INTO @V_INSA

END

 

--커서 CLOSE

CLOSE C_INSA

 

--커서 DEALLOCATE

DEALLOCATE C_INSA

SET NOCOUNT OFF

GO