Exporty a importy dat v SQL serveru z příkazové řádky

Správci databází musí často řešit, jak automaticky importovat a exportovat velká množství dat, která jsou uložena (nebo v případě exportu mají být uložena) v textových souborech s oddělovačem. Jednou z možností, jejíž výhodou je především vysoká rychlost prováděných operací, je použití bulk copy – programu bcp.

Utilita bcp, která se nainstaluje spolu s SQL serverem, kopíruje data mezi instancí SQL serveru a datovým souborem, v němž jsou data uložena ve specifikovaném formátu. Program bcp s příslušnými parametry se spouští z příkazové řádky (command promptu), a proto je možné tyto příkazy zařadit např. do BATCH souborů. Navíc program bcp lze použít i jako klientský nástroj pro ovládání SQL serveru z UNIXu, pokud je na UNIXu nainstalovaný Microsoft ODBC ovladač.

Jak tedy vyexportovat například tabulku jobs z databáze pubs SQL serveru? Zapište do příkazové řádky:

Za názvem programu bcp následuje úplná cesta k tabulce databáze (pubs..jobs) a klíčové slovo „out“, které udává, že se jedná o export. Následuje název výstupního souboru (out.txt) – název souboru může obsahovat i plnou cestu k adresáři, do něhož má být vložen.

Dále jsou uvedeny přepínače, které řídí běh programu:

-U je username pro přihlášení k SQL serveru – v příkladu se příhlašujeme jako uživatel sa;
-P je password pro přihlášení k SQL server – v příkladu se přihlašujeme bez hesla (což si ovšem můžeme dovolit jen v příkladu);
-C udává, v jaké kódové stránce budou vyexportovaná textová data, tj. ta, která jsou uložena v polích datových typů char, varchar a text – v příkladu je použita kódová stránka 1250;
-n znamená, že export bude proveden v nativních datových typech polí SQL serveru; tj. např. číselná pole nebudou převáděna na text a hodnoty tudíž nebudou čitelné třeba v Notepadu;
-t udává oddělovač polí ve výstupním souboru – v příkladu použit středník;
-r udává oddělovač řádků tabulky – v příkladu použit přechod na nový řádek;
-S udává název serveru – použito „servername“.

Při psaní přepínačů záleží na velikosti (case sensitive). Další přepínače se stručným popisem získáte příkazem:

Obecná syntaxe pro použití bcp programu je:

Následuje seznamem přepínačů:

-m max_errors
Udává počet chyb, po kterých je běh programu stornován. Každý řádek, který nemůže být programem bcp kopírován, je započítán jako chyba. Výchozí hodnota přepínače je 10.

-f format_file
Udává úplnou cestu k souboru s formátováním (viz výše). Výchozí název souboru Bcp.fmt.

-e err_file
Udává úplnou cestu k chybovému souboru. Používá se k ukládání řádků, které program bcp nemohl zpracovat. Při nepoužití přepínače není chybový soubor vytvořen.

-F first_row
Udává číslo prvního řádku, od kterého začíná bcp zpracovávat data. Výchozí hodnota je 1 (první řádek dat).

-L last_row
Udává poslední řádek, u kterého skončí zpracování dat programem bcp. Výchozí hodnota je 0 (poslední řádek dat).

-b batch_size
Udává počet řádků, které bude SQL server zpracovávat v rámci jedné transakce. Výchozí nastavení je takové, že veškerá data jsou zpracovávána jako jediná transaskce.

-n
Udává, že export bude proveden v nativních datových typech polí SQL serveru.

-w
Definuje použití Unicodu. \t (tab) je řídícím znakem pro oddělení polí, \n (newline) pro oddělení řádků.

-N
Definuje použití nativních datových typů pro netextové položky a Unicod pro položky textové.

-V (60 | 65 | 70)
Definuje použití datových typů z dřívějších verzí SQL serveru (verze 6.0, 6.5 a 7.0).

-q
Nastaví parametr SET QUOTED_IDENTIFIERS ON pro aktivní spojení mezi bcp a instancí SQL serveru.

-C code_page
Určuje kódovou stránku dat. Možné hodnoty:
“ ACP – ANSI/Microsoft Windows® (ISO 1252);
“ OEM – použita default code page užívaná klientem;
“ RAW – bez konverze;
“ číslo – číslo příslušné kódové stránky (viz uvedený příklad).

-t field_term
Udává oddělovač polí. Předdefinovaný je tabulátor (\t).

-r row_term
Udává oddělovač polí. Předdefinovaný je tabulátor (\n).

-i input_file
Udává název souboru, který obsahuje odpovědi na otázky bcp programu, které se jinak objevují v promptu.

-o output_file
Název (a cesta) k výstupnímu souboru.

-a packet_size
Udává počet bajtů posílaných ze serveru v rámci jediného paketu. Povolené hodnoty jsou od 4096 (předdefinovaná hodnota) do 65535. Zvýšení této hodnoty vede k rychlejším přenosům dat.

-S server_name[\instance_name]
Udává SQL server, k němuž se bcp program připojí.

-U login_id
Udává login ID použité pro spojení s SQL serverem.

-P password
Heslo pro použité login ID.

-T
Spojení s SQL serverm realizováno jako trusted connection bez použití login ID a hesla.

-v
Reports the bcp utility version number and copyright.

-R
Udává, že datové typy currency, date, and time jsou zpracovávány ve formátu udaného regionálním nastavením počítače.

-k
Udává, že s prázdným sloupcem bude nakládáno jako by měl hodnotu Null.

-E
Udává, že pro import dat do sloupce, který vlastnost Identity nastavenu na Yes se mají naimportovat data tak, jak jsou zapsaná v externím souboru. Pokud je tento přepínač vynechán, data se naplní automaticky unikátními hodnotami, kde Identity Seed (první hodnota) a Identity Increment (zvýšení) berou z definice příslušné tabulky. Tj. data tohoto sloupce se vytvoří znova a hodnoty v souboru budou ignorovány.
-h „hint [,…n]“
Specifikuje dodatečná nastavení bcp programu podle následující tabulky

Při exportu lze použít mimo názvu tabulky i SQL dotaz

Pokud nepoužijete přepínač -n, bude se program bcp ptát na konkrétní nastavení formátu exportu každého pole. Výhodou tohoto přístupu je, že na závěr je vytvořen textový soubor (jeho název si může uživatel zvolit) obsahující formát exportu. Tento soubor pak lze použít při dalších exportech. K tomu se používá přepínač -f následovaný názvem souboru s definicí formátu exportovaných dat (viz příklad). Tento soubor je možné modifikovat a dosáhnout tak požadovaných výsledků při dalších exportech nebo importech.

Příklad souboru s formátem dat pro export a import:

Na prvním řádku je verze serveru (8.0), ve druhém je počet exportovaných sloupců. V každém dalším řádku je formát jednoho datového pole.

Postupně jsou zde zapsány tyto údaje:
– pořadí, v jakém je pole ukládáno do exportovaného souboru;
– datový typ pole v textovém souboru;
– délka prefixu;
– velikost pole;
– oddělovač použitý za daným polem – pokud má být toto pole poslední na řádku, musí být tento oddělovač jiný než u ostatních polí, aby se oddělovač polí odlišil od oddělovače řádků;
– pořadí, v jakém je dané pole v tabulce na SQL serveru – do exportovaného souboru nemusejí být ukládána všechna pole tabulky;
– název pole v SQL serveru;
– datová stránka pro ukládání textu.

Podobná syntaxe jako pro export platí i pro import. Opětovné naimportování exportovaných údajů ze souboru out.txt zajistíte příkazem:

Stejného efektu lze dosáhnout i z Query Analyzeru SQL serveru příkazem:

Kompletní syntaxe SQL příkazu BULK INSERT je:

Význam jednotlivých parametrů je snadno odvoditelný z výše uvedeného popisu parametrů programu bcp.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.