MSSQL Server BULK INSERT Kullanımı

SQL Server’da büyük veri kümelerini içeri aktarmak söz konusu olduğunda BULK INSERT ve BCP (Bulk Copy Program) en sık kullanılan iki araçtır. Her ikisi de yüksek performans sunsa da kullanım senaryoları ve yetenekleri bakımından ayrışırlar. Bu makalede BULK INSERT komutunu görmüş olacağız.

BULK INSERT, bir veri dosyasını doğrudan SQL Server içinden bir tabloya veya görünüme yüklemek için kullanılan T-SQL komutudur. Bulk İnsert komutu işletim sistemi üzerinde bulunan düz metin dosyasından (örneğin CSV), SQL Server tablosuna doğrudan veri okur ve yükler.

İlk olarak veritabanımızın üzerinden bir csv dosyası oluşturalım. Csv formatında veri oluşturmak için MSSQL Server’dan CSV Formatında Veri Çıkarma makalesi okunabilir.

İlgili makale okunarak sizde aşağıdaki ekran resmindeki gibi herhangi bir dosyaya csv uzantısında veri kaydedebilirsiniz.

Verilerimizin aktarıldığı dosyamızın içerisinde verilerin olduğu görülmektedir.

Bu makalede yapacağımız Bulk Insert komutunu kullanmayıp SSMS üzerinden insert işlemi yapabiliriz. Bu işlemin nasıl yapıldığını görmek için ilgili makale okunabilir.

Bu text dosyasını veritabanında bir tabloya kaydetmek için  kolonların yapısı ve türüyle aynı olan bir tablo oluşturuyoruz. Farklı bir veritabanı altında oluşturuyorum.

use BULKDB_Arsiv
GO
CREATE TABLE [dbo].[DB1_Musteriler](
	[MusteriID] [int] IDENTITY(1,1) NOT NULL,
	[Ad] [nvarchar](50) NULL,
	[Soyad] [nvarchar](50) NULL,
	[UrunAdi] [nvarchar](100) NULL,
	[Fiyati] [decimal](10, 2) NULL,
	[Sehir] [nvarchar](50) NULL,
	[Tarih] [nvarchar](50) NULL
) ON [PRIMARY]

Not: Verilerinizin kaydedileceği yeni tabloda primary olan kolonun kaldırılması gerekmektedir. Ayrıca tarih kolonu aktarımda yaşanan sıkıntıdan dolayı tür değişimi yapıldı. Daha sonra convert veya cast işlemiyle dönüşüm işlemi yapılabilir.

Şimdi csv formatımızda olan verilerimizi Bulk Insert komutuyla tablomuza aktarmış olalım. Bunun için aşağıdaki komut kullanılmaktadır.

BULK INSERT [BULKDB_Arsiv].[dbo].[DB1_Musteriler]
FROM 'C:\BULK_INSERT\BulkInsertFile.txt'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- Sütunlar arasındaki ayırıcı
    ROWTERMINATOR = '|\n',   -- Satır sonu
    CODEPAGE='1254',
    TABLOCK,
    KEEPNULLS -- Boş alanlar NULL olarak kalır
);

Şimdi yukarıdaki kodumuzun satırların ne işe yaradığını görelim.

FIRSTROW = 2 =>1. satır başlık, 2. satırdan başla anlamındadır. Başlık satırımız olmasaydı bu kolonumuz eklenmezdi.

\n: Yeni satır anlamına gelmektedir. Bir aşağı anlamına gelmektedir. | karakterinden sonra kullanılması gerekmektedir. Bir başka kullanım şekli |\r\n‘dir.

Not: \t tab komutuna karşılık gelmektedir.

TABLOCK: Daha hızlı yükleme için tabloyu kilitler (performans artışı sağlar)

CODEPAGE=’1254′: Türkçe karakter sorunu yaşamamak için kullanılır.

İlgili kullanıcımız yukarıdaki kodu çalıştırdıktan sonra aşağıdaki ekran resmindeki gibi hata mesajı almış olur.

Bulk Insert yapacağımız kullanıcının veritabanı üzerinden insert yetkisi olmadığını göstermektedir.

İlgili kullanıcıya insert yetkisi verdikten sonra tekrardan bulk işlemini gerçekleştirdiğimizde sunucu düzeyinde ilgili kullanıcının bulk işlemini aktif etmemiz gerekmektedir. Bu yetki ADMINISTER BULK OPERATIONS  yetkisidir.

Bu işlemler sonucunda tekrardan bulk insert komutunu çalıştırdığımızda verilerimizin yeni veritabanı altındaki tabloya kaydedildiğini görmüş oluyoruz.

Tablomuz çok büyük belirli satırları yükleme işleme arşiv veritabanına yapılacaksa kod aşağıdaki gibi olmalıdır. Ayrıca hata mesajları ile ilgili önlem alabiliriz.

BULK INSERT [BULKDB_Arsiv].[dbo].[DB1_Musteriler2]
FROM 'C:\BULK_INSERT\BulkInsertFile.txt'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- Sütunlar arasındaki ayırıcı
    ROWTERMINATOR = '|\n',   -- Satır sonu
    LASTROW = 501, -- 2.satırda başla 501. satırdan önceki verileri al 
    CODEPAGE='1254',
    TABLOCK,
    KEEPNULLS, -- Boş alanlar NULL olarak kalır
    MAXERRORS = 100, -- Belirli sayıda hatayı tolere et
    ERRORFILE = 'C:\hata_satirlari.txt' -- Hatalı satırları kaydet
);

Arşivleme işlemi yapıldıktan sonra gerçek veritabanındaki değerler silinmiş olur.

Avantajları

• Veriyi satır satır işlemek yerine bloklar halinde yüklediği için geleneksel INSERT komutlarından çok daha hızlıdır.
• Tamamen SQL Server içinde çalışır. Bu sayede Stored Procedure veya SQL scriptleri içine kolayca dahil edilebilir.
• Harici bir araç gerektirmez; SSMS üzerinden veya bir SQL Query penceresinden doğrudan çalıştırılabilir.
• Veritabanı kurtarma modeli (Recovery Model) “Simple” veya “Bulk-Logged” ise, minimum loglama yaparak disk alanından tasarruf sağlar.

Dezavantajları

• Verinin SQL Server’ın erişebildiği bir diskte (lokal veya ağ paylaşımı) olması gerekir. İstemci bilgisayarındaki bir dosyayı doğrudan sunucuya basamaz.
• BULK INSERT sadece veriyi içeri aktarabilir (Import); veriyi bir dosyaya dışarı aktarmak (Export) için kullanılamaz.
• Büyük dosyalarda hata oluştuğunda, hangi satırın hatalı olduğunu bulmak bazen karmaşık olabilir (ancak ERRORFILE parametresi ile bu aşılabilir).

BULK INSERT ve BCP Arasındaki Farklar

BCP, SQL Server ile birlikte gelen komut satırı tabanlı bir araçtır (EXE). Temel farkları aşağıdaki tabloda görebilirsiniz:

Özellik	BULK INSERT	BCP (Bulk Copy Program)
Çalışma Ortamı	SQL Server (T-SQL) içinden çalışır.	Komut istemi (CMD/PowerShell) üzerinden çalışır.
Yön	Sadece Import (İçe aktarma) yapar.	Hem Import hem Export (Dışa aktarma) yapar.
Dosya Konumu	Dosyanın sunucu tarafında olması gerekir.	Dosya istemci bilgisayarında olabilir; veriyi ağ üzerinden gönderir.
Kullanım Kolaylığı	SQL geliştiricileri için daha pratiktir.	Sistem yöneticileri ve otomasyon (Batch script) için daha uygundur.
Format Dosyası	Format dosyalarını destekler.	Format dosyalarını hem kullanabilir hem de oluşturabilir.

Full Recovery Model altındaki bir veritabanında yapılan BULK INSERT veya BCP işlemleri, işlem günlüğüne (Transaction Log) tam olarak kaydedilir (Fully Logged).

Eğer yüklediğiniz veri miktarı çok büyükse, log dosyası hızla büyür ve disk alanını tüketerek dolabilir. Bu durum gerçekleştiğinde SQL Server işlemi durdurur ve hata verir.

Eğer modeli değiştiremiyorsanız, veriyi tek bir dev işlem yerine küçük parçalar halinde yüklemelisiniz.

• BATCHSIZE = 10000 gibi bir parametre eklerseniz, SQL Server her 10.000 satırda bir işlemi “commit” eder.
• Bu sayede, eğer periyodik Log Backup (Transaction Log yedeği) alınıyorsa, dolan kısımlar yedekleme sonrası boşalır ve log dosyasının kontrolsüz büyümesi engellenir.

Aşağıdaki script, veriyi 10.000’er satırlık paketler halinde yükler ve her paket arasında 5 saniye bekler.

DECLARE @StartRow INT = 2;
DECLARE @BatchSize INT = 10000;
DECLARE @MaxRows INT = 1000000;
DECLARE @CurrentLastRow INT;

WHILE @StartRow < @MaxRows
BEGIN
    SET @CurrentLastRow = @StartRow + @BatchSize - 1;
    
    IF @CurrentLastRow > @MaxRows SET @CurrentLastRow = @MaxRows;

    EXEC(
    'BULK INSERT [BULKDB_Arsiv].[dbo].[DB1_Musteriler2]
    FROM ''C:\BULK_INSERT\BulkInsertFile.txt''
    WITH (
        FIRSTROW = ' + @StartRow + ',
        LASTROW = ' + @CurrentLastRow + ',
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''|\n'',
        CODEPAGE = ''1254'',
        TABLOCK,
        KEEPNULLS,
        MAXERRORS = 100,
        ERRORFILE = ''C:\hata_satirlari_' + CAST(@StartRow AS VARCHAR) + '.txt''
    )');

    SET @StartRow = @CurrentLastRow + 1;

    IF @StartRow < @MaxRows
    BEGIN
        WAITFOR DELAY '00:10:00'; 
    END
END

Bu makalemizde bulk insert komutunu detaylı bir şekilde görmüş olduk. Başka bir makalede görüşmek dileğiyle..

“İnsanlara merhamet etmeyene Allah merhamet etmez.” (Müslim)