#title OpenDataSource

{{{
SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=192.168.0.1,1433;User Id=아이디;Password=패스워드')
    .DB명.스키마명.테이블명

}}}

{{{
insert into 
opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data Source={Your Excel File Path};Extended Properties=Excel 8.0')...[SheetName$]
values (....)
}}}


{{{
Create proc sp_write2Excel (@fileName varchar(100),

                                   @NumOfColumns tinyint,

                                   @query     varchar(200))

as

begin

        declare @dosStmt  varchar(200) 

        declare @tsqlStmt varchar(500) 

        declare @colList  varchar(200) 

        declare @charInd  tinyint 

        

        set nocount on

 

        -- construct the  columnList A,B,C ... 

        -- until Num Of columns is reached.

 

        set @charInd=0

        set @colList = 'A'

        while @charInd < @NumOfColumns - 1

        begin 

          set @charInd = @charInd + 1

          set @colList = @colList + ',' + char(65 + @charInd)

        end 

 

        -- Create an Empty Excel file as the target file name by copying the template Empty excel File

        set @dosStmt = ' copy c:      empempty.xls ' + @fileName

        exec master..xp_cmdshell @dosStmt

 

        -- Create a "temporary" linked server to that file in order to "Export" Data

        EXEC sp_addlinkedserver 'ExcelSource', 

        'Jet 4.0',

        'Microsoft.Jet.OLEDB.4.0',

        @fileName,

        NULL,

        'Excel 5.0'

 

        -- construct a T-SQL statement that will actually export the query results

        -- to the Table in the target linked server 

        set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' +  ' ( ' + @colList + ' ) '+ @query

        

        print @tsqlStmt

 

        -- execute dynamically the TSQL statement

        exec (@tsqlStmt)

 

        -- drop the linked server 

        EXEC sp_dropserver 'ExcelSource' 

        set nocount off

end 

GO

}}}