Utilītprogramma: bcp. Datu eksports/imports

Kad ir vajadzība datubāzes datus eksportēt uz ārēju failu vai importēt no faila datubāzē, datu eksportam un importam no teksta failiem SQL Server iespējams izmantot bcp utilītu, kura bez problēmām tiek galā arī ar liela apjoma datu apstrādi.

Datu eksports
Veicot eksportu no datubāzes ar bcp utilītu, rezultātā tiek iegūts txt fails.
Datu eksportam no datubāzes ar bcp ir šāda komandrindas sintakse:

bcp Mydb.dbo.MyTable out c:\temp\export.txt -S SERVERNAME -T –n

Izskatās šādi:

Ir iespējams arī eksportēt nevis visus datus no kādas tabulas (kā augstāk redzamajā piemērā), bet tikai interesējošos datus, atlasot ar vaicājumu:

bcp "SELECT TOP 100 * FROM Mydb.dbo.MyTable" queryout c:\temp\export.txt -S SERVERNAME -T –n

Datu formāta fails
Lai failu varētu veiksmīgi importēt atpakaļ (vai arī citā datubāzē), ir ļoti vēlams formāta fails- fails, kas satur informāciju par laukiem, to datu tipiem un garumiem, kolonnu un rindu atdalītājiem - kas palīdzēs SQL serverim saprast eksportētā datu faila struktūru.
Formāta failu var uzģenerēt līdzīgā veidā kā tika eksportēti dati, pamainot parametrus:

bcp Mydb.dbo.MyTable format nul -n -x -f c:\temp\format.xml -T -S SERVERNAME

Parametrs "–n" nozīmē, ka dati tiks eksportēti native formātā (native formāts ir labāks tajā ziņā, ka dati tiek eksportēti izmantojot SQL Server datu tipus un nav nepieciešamas papildus tipu konvertācijas. šādus failus ir ērti izmantot ja dati vēlāk arī jāimportē SQL Server datu bāzē). Iespējams eksportēt datus arī character formātā, aizstājot "–n" parametra vērtību ar "–c", kurai papildus iespējams norādīt arī kodējumu- šī opcija varētu būt nepieciešama, ja eksportēto failu paredzēts izmantot kādā citā ārējā programmā vai arī tad, ja datiem ir jābūt cilvēkam viegli uztveramam. Taču, kā minēts, ja paredzēts eksportēto datu failu pēc tam importēt SQL Server datu bāzē, tad ieteicams datus labāk eksportēt native formātā (vairāk par formāta parametriem šeit un par ne-xml formāta failiem).

Datu imports

Lai importētu (vai arī vienkārši apstrādātu) datus no eksportētā datu faila, var izmantot SQL OPENROWSET operatoru:

SELECT * FROM OPENROWSET
(BULK 'c:\temp\export.txt ', FORMATFILE= 'c:\temp\ format.xml ') AS RS

Ja nepieciešams eksportētos datus importēt pa taisno datubāzē un saglabāt to identity kolonnas vērtības, tad nepieciešams, piemēram, šāds vaicājums:

INSERT INTO MyTbl
WITH (KEEPDEFAULTS, KEEPIDENTITY) ([Id],[col_1],[col_2])
SELECT [Id],[col_1],[col_2] FROM
OPENROWSET( BULK 'c:\temp\export.txt', FORMATFILE='c:\temp\format.xml') AS RS
WHERE RS.Id NOT IN (SELECT Id FROM MyTbl)

Ja ir izeksportēta datu apakškopa un to nepieciešams importēt atpakaļ SQL Server, iespējams manuāli pamainīt formāta failu, tajā atstājot tikai nepieciešamās kolonnas, vai arī pēc vajadzības pievienojot klāt jaunas. XML Formāta fails ir ar visai vienkāršu un skaidri saprotamu struktūru:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/
bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2"
MAX_LENGTH="60" COLLATION="Latin1_General_CI_AI"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="Cnt" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Name" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Ja gadās, ka importējot datus ar ģenerēto formāta failu, tiek iegūta sekojoša kļūda:

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 695, column 12. Verify that the field terminator and row terminator are specified correctly.

Šajā gadījumā iemesls ir tāds, ka ģenerējot formāta failu, ne visām kolonnām formāta failā tiek pielikts maksimālais garums – to iespējams atrisināt, manuāli pieliekot formāta failā šo atribūtu attiecīgajai kolonnai. Īpaši gadījumos, kad eksportējamos datos ir kāda kolonna ar xml datu tipu, ģenerētajā formāta failā šis MAX_LENGTH atribūts netiek izveidots – ieteicams to pievienot un kā vērtību norādīt "8001" (nav ne jausmas kāpēc tieši tik, bet xml lauka gadījumā bez šīs vērtības imports meta kļūdu :) ).