SQL Server sahip olduğu Export/Import aracıyla diğer veritabanlarına göre bir adım önde diyebiliriz. SQL Server üzerinde çalışırken sorgu sonucunu Excel formatında dışarı çıkarmak her zaman ihtiyaç duyulan bir durum olmuştur. Excel’de SQL Server’e import veya ters yönde export işlemi için en çok kullanılan yöntemler SQL 2000’de Data Transformation Services (DTS), SQL 2005’te SQL Server Integration Services (SSIS) ve her iki sürümde Bulk Copy (BCP) araçlardır. Bu araçların özelliği grafiksel ortam sunmaları ve genellikle Job’lar aracılığıyla çalıştırılıyor olmasıdır. DTS, SSIS ile nasıl yapılacağı kolay bir işlemdir. Bu yazıda BCP ve farklı T-SQL alternatiflerini inceleyeceğiz.
Excel’den SQL Server’e Veri Aktarımı
Excel’den SQL Server’e programatik olarak veri aktarmanın Linked Server veya Distributed Query olmak üzere iki yolu mevcuttur. SQL Server, Linked Server aracılığıyla diğer OLE DB tabanlı veri kaynaklarıyla iletişim kurabilme yeteneğine sahiptir. Microsoft Excel workbookları da OLE DB tabanlıp olup uzatkan SQL diliyle sorgulanabilir yapıya sahiptir. SQL Server Management Studio, Enterprise Manager, SQL-DMO (Distributed Management Objects) veya SMO (SQL Server Management Objects) araç ve bileşenleri kullanarak bir Excel kaynağını bir SQL Server linked server olarak tanımlayabiliriz. Linked server tanımlanırken öncelikle linked server adı tanımlanır ardından bağlantıya uygun OLE DB Provider seçimi yapılır ve provider string düzenlenir. Provider string, erişilecek veri kaynağı hakkında bilgi içerir. Linked server oluşturmak sp_addlinkedserver stored procedure kullanılır. Aşağıdaki adımlarda mevcut yöntemler kullanılarak Excel’in bir linked server olarak nasıl yapılandırılacağı anlatılmıştır.
Enterprise Manager (SQL Server 2000)

• Enterprise Manager içerisinde Security klasörünü açalım.
• Linked Servers‘i sağ tıklayıp “New linked server”i tıklayalım.
• Öncelikle linked server için bir isim tanımlayalım.
• Server type alanından Other data source seçeneğini aktifleştirelim.
• Provider name listesinden Microsoft Jet 4.0 OLE DB Provider sağlayıcısını seçelim.
• Data source bölümüne Excel dosyasını konumunu girelim.
• Provider string bölümüne Excel 2002, Excel 2000 veya Excel 97 için Excel 8.0 yazalım.

SQL Server Management Studio (SQL Server 2005)

• Management Studio içerisinde Object Explorer bölümünden Server Objects klasörünü açalım.
• Linked Servers‘i sağ tıklayıp “New linked server”i tıklayalım.
• General sekmesinde linked server için bir isim tanımlayalım.
• Server type alanından Other data source seçeneğini aktifleştirelim.
• Provider name listesinden Microsoft Jet 4.0 OLE DB Provider sağlayıcısını seçelim.
• Product name bölümüne Excel yazalım.
• Data source bölümüne Excel dosyasını konumunu girelim.
• Provider string bölümüne Excel 2002, Excel 2000 veya Excel 97 için Excel 8.0 yazalım.

sp_addlinkedserver yordamını kullanarak linked server oluşturmak

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Parametreleri yapılandıralım
SET @server = 'ExcelDosya'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\Book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct,
@provider, @datasrc, @location, @provstr, @catalog

SQL-DMO bileşenini kullanarak linked server oluşturmak
Visual Basic 6.0 veya .NET uygulamalarında DMO (SQL Distributed Management Objects) bileşenini kullanarak Excel kaynaklı bir linked server oluşturulabilir.

Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub

SMO bileşenini kullanarak linked server oluşturmak
Bilindiği gibi SQL Server 2005 ile birlikte SQL Server Management Objects (SMO) bileşeni geliştirildi. .NET uygulamalarında Microsoft.SqlServer.Management.Smo ve Microsoft.SqlServer.Management.Common kütüphanelerini kullanarak programatik olarak SQL Server’i yönetebiliriz. Aşağıdaki örnekte SMO bileşeni kullanılarak Excel dosyasına bağlı linked server oluşturulmuştur.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("Sunucu Adi", "Kullanici Adi", "Sifre")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Bu yöntemlerden birini kullanarak Excel linked server kurduktan sonra bu bağlantıyı nasıl kullanabileceğimizi örneklendirelim. T-SQL içerisinde linked server içindeki bir nesneye erişileceği zaman ..< şema adı="">. yolu kullanılır. Excel’deki sheet’ler tabloları temsil eder. Tablo isimlerinin sonuna $ işareti eklenir.

SELECT * FROM ExcelDosya...Sheet1$

Eğer bu aşamada OLE DB provider “Microsoft.Jet.OLEDB.4.0¨ for linked server “ExcelDosya” returned message “Cannot start your application. The workgroup information file is missing or opened exclusively by another user.”. şeklinde bir hata mesajı alınırsa bu sorguyu çalıştıran kullanıcının yetkilerinin kontrol etmenizi tavsiye ederim.
Farklı bir yazım biçimi olarak OPENQUERY fonksiyonunu da kullanabiliriz.

SELECT * FROM OPENQUERY(ExcelDosya, 'SELECT * FROM [Sheet1$]')

Bu arada yeri gelmişken not almamızda fayda var. Excel belli bir kolon aralığını seçmek için “SELECT * FROM [Sheet1$A1:B10]” ifadesi kullanılır.
Bir linked server içindeki tüm tabloları listelemek için SP_TABLES_EX yordamı ve linked server’in ayakta olup olmadığını test etmek için sp_testlinkedserver yordamı kullanılabilir.
Distributed Query Kullanmak
SQL Server üzerinde kalıcı bir linked server kurmadan distributed query aracılığıyla da OLEDB tabanlı kaynaklarda sorgulama yapılabilir. Bunun için OPENDATASOURCE veya OPENROWSET fonksiyonları kullanılır. SQL Server 2005 kullanılıyorsa “SQL Server Surface Area Configuration” aracı kullanılarak Ad Hoc Distributed Queries seçeneğinin aktifleştirilmesi gerekir.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\Rapor.xls;Extended Properties=Excel 8.0')...Sheet1$

OPENROWSET() fonksiyonunu aşağıdaki gibi kullanıyoruz.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 8.0;Database=c:\Rapor.xls', Sheet1$)

Veya aşağıdaki gibi belli alanları da seçebiliriz.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
	'Excel 8.0;Database=c:\rapor.xls', 'SELECT * FROM [Sheet1$]')

Bu yöntemlerde Excel’den okuduğumuz verileri SELECT..INTO ile herhangi bir SQL tablosuna aktarabiliriz.
SQL Server’den Excel’e Veri Aktarımı
SQL Server’de bir sorgunun sonucunu Excel’de aktarmak için Excel tarafında bazı yöntemler olduğu gibi SQL Server tarafında da bazı yöntemler bulunmaktadır. Excel’deki yöntemlerin başında Data menüsünün kullanılmasıdır. Excel programında “Data » Import External Data » New Database Query” bölümü kullanılarak ODBC üzerinde tanımlı data source’ler aracılığıyla SQL Server dahil olmak üzere farklı kaynaklara erişilebilir.

Bu menünün açtığı ekrandan sorgular tasarlanabilir. Excel’e veri almanın diğer yolu ise makro yazmaktır. ADO kütüphanesi kullanılarak VB kodları aracılığıyla uzaktaki veya yakındaki bir SQL Server’e bağlanabilir. Bunun için aşağıdaki adımlar takip edilir.

• Excel programını açıp mevcut dosyayı kayıt edelim.
• “Visual Basic Editor” aracını açalım. Bunun için “Tools » Macro » Visual Basic Editor (ALT+F11)” menüsü kullanılabilir.
• Sol taraftan uygun VBA projesini seçelim.
• Tools menüsünden References menüsünü tıklayalım.
• Referanslar arasında Microsoft ActiveX Data Objects Library kütüphanesinin güncel versiyonunu projeye ekleyelim.
• Sol taraftaki proje altında yeni bir module seçip DataAktar isminde bir yordam oluşturup yordamın içerisine aşağıdaki kodları yazalım.

Sub DataAktar()
    ' Connection nesnesi oluşturalım.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    ' Bağlantı cümlesi değişkeni
    Dim strConn As String

    ' SQL Server OLE DB Provider sağlayıcısını kullanacağız
    strConn = "PROVIDER=SQLOLEDB;"

    ' Yerel sunucudaki Deneme veritabanına bağlanacağız.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=Deneme;"

    ' Windows Authentication güvenliğini tercih ediyoruz
    strConn = strConn & " INTEGRATED SECURITY=sspi;"

    ' Bağlantıyı açalım
    cnPubs.Open strConn

    ' Bir recordset nesnesi oluşturalım
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

    With rsPubs
        ' Mevcut bağlantı nesnesini tahsis edelim
        .ActiveConnection = cnPubs
        ' Sorgumuzu yazalım
        .Open "SELECT * FROM Musteri"
        ' Kayıtları Sheet1'teki A1 hücresinden itibaren yazdıralım
        Sheet1.Range("A1").CopyFromRecordset rsPubs

        .Close
    End With

    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Sub

SQL Server tarafında herhangi bir sorgunun sonucunu Excel’e aktarmanın ilk yolu INSERT INTO OPENROWSET fonksiyonunu kullanmaktır. Bu fonksiyonu kullanmak için Excel dosyası ve içinde sheet’inin ve ona ait kolonların hazır olması gerekir.

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
      , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ 
Aşağıdaki örnekte içinde Sheet1$ tablosu bulunan Rapor.xls dosyasına Musteri tablosu aktarılmıştır.
[sql]INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
	'Excel 8.0;Database=C:\Rapor.xls;',
	'SELECT MusteriId, AdSoyad FROM [Sheet1$]')
SELECT * FROM Musteri

Eğer Excel dosyası hazır değilse sp_makewebtask yordamı kullanılabilir. Birçok parametre alan sp_makewebtask yordamı bir sorgunun sonucunu dışarıdaki bir dosyaya html formatında kaydeder. Excel dosyası da html kodlarını çözebildiğim için bir excel çıktısı oluşmuş olacaktır.

EXEC sp_makewebtask
	@outputfile = 'C:\Rapor1.xls',
	@query = 'Select * from Musteri',
	@colheaders =1,
	@FixedFont=0,@lastupdated=0,@resultstitle='Başlık'

Aynı şekilde Excel dosyasını çalışma anında oluşturmak için BCP (Bulk Copy Program) aracı da kullanılabilir. SQL Server’in haricinde çalışan komut sistemi tabanlı BCP aracı, SQL Server ile kullanıcının belirlediği dosya arasında data aktarımını gerçekleştirir. Toplu aktarım işleminin hangi yönde olacağı in, out| , queryout | , format seçenekleriyle belirtilir.

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

BCP aracını kullanırkan parametrelerin büüyük küçük harf duyarlılığına sahip olduğunu unutmamalıyız. “S” parametresi bağlanılacak sunucuyu belirtmek için, “T” parametresi Trusted Connection için kullanılır SQL Server Authentication için kullanıcı adı ve şifre “U” ve “P” parametreleriyle belirtilir. “c” parametresi dosyanın Char (ASCII) tipinde olmasını yani kolonlar arasına field terminator (tab), satırlar arasına row terminator (crlf / New Line) karakteri konulur. İki SQL Server arasındaki aktarım olacaksa -c yerine -n veya -N parametresinin kullanılması daha doğru olacaktır (-N = Unicode). Bu parametre iki sistem arasındaki dönüştürme işlemlerini hızlandırır.
Yerel sunucuda bulunan Musteri tablosunu Liste.xls dosyasına aktaralım.

Exec Master..xp_cmdshell 'bcp Deneme..Musteri out "C:\Liste.xls" -T -c'

Aynı dosyayı kullanarak ters yönde aktarma yapalım. Excel dosyasından SQL Server tablosuna import işlemi için “in” seçeneği kullanılır.

bcp Deneme..Musteri in "C:\Liste.xls" -T -c

Sadece belli kolonları veya koşula bağlı satırları göndermek için queryout seçeneği kullanılır.

bcp "SELECT AdSoyad FROM Deneme..Musteri" queryout "C:\Liste.xls" -T -c

“-t” parametresi field terminator’i değiştirmek için kullanılır. “-t,” kolon ayıracının virgül olmasını sağlar.

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -c -t, -T'
exec master..xp_cmdshell @sorgu

1,M1
2,M2
3,
4,
5,M1
6,M2
7,
8,

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -T -t~ -c'
exec master..xp_cmdshell @sorgu

1~M1
2~M2
3~
4~
5~M1
6~M2
7~
8~
Satır ayıracını (row terminator) belirtmek için “-r” parametresi kullanılır.

declare @sorgu varchar(150)
select @sorgu = 'bcp Deneme..Musteri out c:\liste.csv -T -t~ -r; -c'
exec master..xp_cmdshell @sorgu

1~M1 ;2~M2 ;3~;4~;5~M1 ;6~M2 ;7~;8~;
“-r” paramtresine crlf (carriage return, line feed) gibi klavyeden girilemeyen değerler için hex değeri girilebilir.
Aşağıdaki örnekte mevcut database içerisindeki tüm tablolar için BCP komut cümlesi oluşturulmuştur.

select 'exec master..xp_cmdshell'
		+ ' '''
		+ 'bcp'
		+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
		+ ' out'
		+ ' c:\Yedek\'
		+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.bcp'
		+ ' -N'
		+ ' -T'
		+ ' -S' + @@servername
		+ ''''
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

BULK INSERT Komutu
Kullanıcının belirlediği bir flat dosyadan SQL tablosuna data aktarmak için kullanılır. BCP komutundan farklı olarak sadece import işlemini destekler.

BULK INSERT
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE  =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] FIRSTROW  =first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]
   [ [ , ] ERRORFILE = 'file_name' ]
    )] 

Aşağıdaki örnekte BCP aracı kullanılarak Musteri tablosu bcp dosyasına yazdırılmış ve ardından BULK INSERT komutu aracılığıyla bcp dosyasından Musteri tablosuna ters aktarım yapılmıştır.

exec master..xp_cmdshell 'bcp Deneme..Musteri out C:\Musteri.bcp -c -T'

BULK INSERT Deneme..Musteri FROM 'C:\Musteri.bcp'

Bu komutu kullanarak standartlardan farklı Row Delimeter veya Column Delimeter kullanılmışsa bu ayıraçları da belirtme imkanımız bulunmaktadır.

BULK INSERT Deneme..Musteri FROM 'C:\Musteri.bcp'
	WITH (FIELDTERMINATOR = ':',ROWTERMINATOR = '\t')

Ayrıca bu komuta ait CHECK_CONSTRAINTS parametresi kullanılarak mevcut constraintlerin etkin olup olmayacağı da belirtilebilir.