O SQL Server έχει αρκετά εργαλεία για να κάνεις export & import data. Από τα απλά T-SQL BULK INSET ή το κλασσικό BCP μέχρι τα DTS (Data Transformation Services) και σήμερα τα SSIS ( SQL Server Integration Services).
Όμως υπάρχουν αρκετές περιπτώσεις που δεν θα ήθελες να εμπλακείς με αυτά.
Ένα σενάριο το οποίο κάποια στιγμή σε ένα project μου έτυχε ήταν η απαίτηση να πατάει ο χρήστης ένα κουμπί και να γίνονται τα δεδομένα export σε Excel αρχείο.
Για να δούμε λοιπόν την υλοποίηση του χωρίς να χρησιμοποιήσω κάτι από τα παραπάνω αλλά κάνοντας χρήση αγνού T-SQL και της δυνατότητας που έχει ο SQL Server με τους Linked Servers.
Βασική προϋπόθεση για να μπορέσει η λύση να δουλέψει είναι να δημιουργήσεις ένα άδειο αρχείο excel στο server με συγκεκριμένο όνομα και σε συγκεκριμένο directory. Ας το πούμε empty.xls και ας το βάλουμε στο c:\temp. Αυτό θα λειτουργεί σαν template για τα αρχεία που δα δημιουργήσουμε παρακάτω.
Όμως δεν φτάνει μόνο αυτό.
Θα πρέπει να ανοίξω το excel αρχείο και να δώσω ένα όνομα στο φύλλο (πχ MyData).
Ακόμα θα πρέπει να γνωρίζω πόσα πεδία θα βάλω στο excel φύλλο πχ αν ξέρω ότι θα βάλω 2 πεδία θα πρέπει στα πρώτα 2 κελιά της πρώτης γραμμής να δώσω κάποιο όνομα πχ A,B.
Φτιάχνω την stored procedure που θα κάνει όλη την δουλειά
create proc usp_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
--
-- COLUMNS LIST CREATION
--
set @charInd=0
set @colList = 'A'
while @charInd < @NumOfColumns - 1
begin
set @charInd = @charInd + 1
set @colList = @colList + ',' + char(65+ @charInd)
end
-- CREATE MY EXCEL FILE BY COPING EXCEL TEMPLATE
set @dosStmt = ' copy c:\temp\empty.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...[MyData$] ' + ' ( ' + @colList + ' ) '+ @query
-- execute dynamically the TSQL statement
exec (@tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
end
GO
Execute sp
exec usp_write2Excel 'c:\temp\Customers.xls',2,'select customerid,companyname from northwind.dbo.customers'
Αυτό ήταν έχεις τα δεδομένα σου σε Excel!!!