Prebacivanje podataka iz jedne tabele u drugu je nešto što svaki DB developer radi manje-više redovno. Povremeno se, kod tog procesa, može pojaviti i dodatna komplikacija da svi preneti redovi moraju dobiti nove PK u novoj tabeli. Razlozi za to mogu biti različiti: da u ciljnoj tabeli već postoje redovi koji imaju iste ID-eve kao i u izvornoj, da se razlikuje IDENTITY SEED dve tabele, da se razlikuju tipovi podataka PK-a ili čak da se razlikuje i broj PK kolona. U svakom slučaju, najčešće je potrebno usput sačuvati i vezu između novih i starih ID-eva u nekoj tabeli za mapiranje.

Ovaj članak će opisati rešenje za tu situaciju: Prebacivanje redova iz jedne tabele u drugu, gde se u destinacionoj tabeli dodeljuju novi ID-evi, a pritom čuvamo vezu u trećoj tabeli.

Kao primer uzećemo tabelu dbo.CrtaniFilmovi iz naše baze koja kao primarni ključ ima kolonu CrtacID i pored nje još dve kolone sa opisom i nazivom.

Zadatak bi nam bio da prebacimo podatke o crtanim filmovima u neku tabelu dbo.Cartoons koja generiše svoje ID-eve u CartoonID koloni (po drugačijem seed-u). Usput bi trebalo sačuvati i vezu između postojećih slogova i novih u tabeli dbo.CartoonsCrtaciMapping

CREATE TABLE dbo.Cartoons 
   (CartoonID INT IDENTITY(100,10)
   , CartoonName NVARCHAR(100));

CREATE TABLE dbo.CartoonsCrtaciMapping 
	(CartoonID INT, CrtacID INT);

Postoji nekoliko načina da se ovo uradi i njih nećemo detaljno opisivati u ovom članku, ali ćemo pomenuti i skrenuti pažnju na njihove nedostatke:

1. While petlja ili kursor koji bi prebacivao red po red, uz pomoć SCOPE_IDENTITY() funkcije čitati novokreirane ID-eve i upisivati ih. Možda naizgled najjednostavnije rešenje, ali već na par hiljada redova postaje neprihvatljivo neperformantno.

2. Dodati privremeno kolonu CrtacID u Cartoons tabelu, popuniti je prilikom inserta i ukloniti nakon prebacivanja podataka u Mapping tabelu. Ovo je relativno OK varijanta, ali samo ukoliko postoji mogućnost altera destinacione tabele, što, usled nedostatka prava ili zbog opasnosti da bi ometalo druge upite, neće uvek biti slučaj.

3. Prebaciti podatke prostim INSERT..SELECT bez razmišljanja o mapiranju, a nakon toga uraditi JOIN između destinacione i izvorne tabele po unique kolonama. Prošlo bi u našem primeru sa kolonama CartoonName i Naziv, ali šta ako nemamo unique kolone? Šta ako u već postojećim podacima postoje podaci identični onim koji se ubacuju? Dosta upitnika i dosta rizična varijanta.

4. Aktivirati IDENTITY_INSERT nad ciljnom tabelom i voditi ručno računa o ključevima ili uz pomoć nekih privremenih tabela. Još jedna riskanta varijanta, a koja usput zahteva i da se tabela zaključa i spreči bilo kakav upis sa strane dok traje migracija.

Jedina logična i bezbedna opcija bila bi korišćenje OUTPUT klauzule prilikom inserta, nešto kao u sledećem primeru:

INSERT INTO dbo.Cartoons
OUTPUT INSERTED.CartoonID, CrtaniFilmovi.CrtacID
INTO dbo.CartoonsCrtaciMapping
SELECT Naziv 
FROM dbo.CrtaniFilmovi;

Međutim navedeni upit će vratiti grešku, s obzirom da u OUTPUT-u nije moguće koristiti kolone iz SELECT dela već samo one koje su insertovane.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier „CrtaniFilmovi.CrtacID“ could not be bound.

Problem se prevazilazi tako što se umesto INSERT naredbe može upotrebiti MERGE naredba koja dozvoljava OUTPUT i izvornih podataka. Iako originalna namena MERGE-a nije za prost INSERT, ništa nas ne sprečava da je upotrebimo tako. Samo ćemo za spajanje staviti neki nemogući uslov i u svakom slučaju će se desiti INSERT:

MERGE dbo.Cartoons
USING dbo.CrtaniFilmovi 
	ON 1=2
WHEN NOT MATCHED THEN
	INSERT (CartoonName)
	VALUES (Naziv)
	OUTPUT INSERTED.CartoonID, CrtaniFilmovi.CrtacID
	INTO CartoonsCrtaciMapping;

Izvršavanjem ove naredbe svi crtaći iz naše tabele će biti prebačeni u novu dbo.Cartoons, a usput će i mapping tabela biti popunjena novim i starim ID-evima.

Naravno, ovo je moguće samo ukoliko koristite SQL Server 2008 ili noviji gde postoji komanda MERGE. Ukoliko ste i dalje na 2005 moraćete da primenite neku od gore pomenutih varijanti (2 ili 3 ukoliko je moguće).

Ukoliko imate zahtev da je potrebno stored procedurom vratiti određeni broj redova sortiranih po nekom redosledu ovo je jedan od načina kojim to može da se uradi.
Recimo da je gridu koji podržava sorting i paging potrebno vratiti crtane likove za određeni crtani film zajedno sa ukupnim brojem redova.
U tom slučaju bi kreirali sledeću stored proceduru:

Bliži se kraj godine i jedno pitanje sa kojim se kao developer možda sretnete u to vreme, a pogotovu ako radite sa bazama koje niste samo vi održavali, je „Da li je negde u nekoj proceduri ostala zakucana godina?“

Alata i programa za pretragu procedura, viewova i funkcija ima dosta na tržištu, uglavnom svi funkcionišu dobro, a mnogi su čak i besplatni. Međutim, problem koji se javlja kod svih tih programa je, što u rezultate pretrage uvek ulaze i komentari unutar procedura, odnosno vraćaju među rezultate i procedure gde se termin koji tražite nalazi unutar komentara. To najčešće nije problem, ali kada se traži godina ili datum može biti. Ukoliko redovno vodite u zaglavlju procedura evidenciju izmena, ili imate primere poziva koji u sebi uključuju datum, verovatno da ćete za pretragu termina „2014“ dobiti sve procedure koje ste menjali u toku 2014. godine.

Naravno, postoji i opcija da sami pretražujete sistemske viewove, ali problem ostaje isti.

Da bismo prevazišli problem, mi smo vam pripremili jedan upit – proceduru koja radi pretragu, ali pre toga uklanja sve komentare i pretražuje samo kod.

Pre nekog vremena smo u dva članka (1, 2) opisali proces konkatenacije, odnosno spajanja vrednosti iz kolone u jedan string. Ovog puta prikazaćemo obrnut proces – kako iz jednog csv stringa dobiti sve pojedinačne vrednosti. Kako je ovaj proces malo složeniji i za pojedinačni string treba da vrati tabelu, obično se u te svrhe koriste table-valued funkcije, tzv. splitting funkcije.

Odmah da napomenemo da je najispravnija varijanta za splitting funkciju korišćenje CLR. Međutim, to ćemo ostaviti za neki naredni članak, a u ovom ćemo opisati nekoliko T-SQL rešenja. Njih postoji dosta, a mi smo izabrali pet koje smo prilagodili i adaptirali tako da sve funkcije imaju iste ulazne parametre i vraćaju iste rezultate:

• Prvi ulazni parametar je string koji se splituje, u NVARCHAR(MAX) formatu
• Drugi ulazni parametar je delimiter, koji i sam može imati do 255 znakova
• Ukoliko delimiter nije prosleđen koristi se zarez (,)
• Rezultat funkcije je tabela sa dve kolone: RedniBroj i Element
• Povratni elementi su tipa NVARCHAR(4000)
• Blanko znakovi na početku svakog elementa se uklanjaju

While petlja

Prvo rešenje koje ćemo opisati je ujedno i ono koje se najčešće koristi. Odseca se prvi element, a zatim u while petlji ponavlja proces nad ostatkom stringa.

Identity kolona predstavlja kolonu kojoj SQL Server automatski dodjeljuje vrijednost (broj) iz niza brojeva kojim je definisan. Taj niz je definisan početnom vrijednošću i inkrementom tj. vrijednošću za koju se početna vrijednost uvećava prilikom unosa novog reda.
Prilikom kreiranja tabele, sintaksa za kreiranje identity kolone je sledeća:

CREATE TABLE dbo.TestKefalo
(ID INT IDENTITY(1,1));

Prvi broj u IDENTITY(1,1) predstavlja početnu vrijednost a drugi broj vrijednost inkrementa.
Analogno se u tabeli dodaje identity kolona, naredbom ALTER TABLE ADD.

U jednom od prethodnih članaka (String funkcije (1. deo)) smo opisali neke od najčešće korišćenih funkcija za rukovanje stringovima. Ovaj članak donosi opis nekih manje poznatih funkcija.

SPACE funkcija kao rezultat vraća onoliko praznih mesta koliko prosledimo kao parametar. Sintaksa je SPACE(broj) gde parametar broj predstavlja praznih mesta.
Na osnovu opisanog ponašanja funkcije možemo zaključiti da je rezultat sledećeg upita:

SELECT 'SQL' + SPACE(5) + 'KEFALO';

niz karaktera SQL KEFALO.

CONCAT funkciju koristimo za povezivanje 2 ili više niza karaktera u jedan niz (string). To postižemo sintaksom: CONCAT(string1, string2, string3 … stringN), gde svaki od parametara predstavlja niz karaktera. Neophodno je proslediti najmanje 2 parametra, u suprotnom će se javiti greška. Pre povezivanja parametara, vrši se njihova implicitna konverzija u string. NULL vrednosti se implicitno konvertuju u prazan string. Tip podatka rezultata zavisi od parametara koje smo prosledili. Ukoliko je bar jedan od parametara UNICODE tipa (nchar/nvarchar) rezultat će biti UNICODE. U suprotnom će rezultat biti char/varchar. Što se tiče dužine, i ona zavisi od ulaznih parametara. Ako bar jedan od stringova na ulazu ima dužinu MAX (npr. varchar(max)) i rezultat će imati dužinu MAX. U ostalim slučajevima dužina rezultata je jednaka zbiru dužina pojedinačnih stringova.
Ukoliko izvršimo upit:

Ukoliko dobijete ideju da proverite da li u nekoj BIT koloni postoji makar jedna vrednost true ili makar jedna vrednost false, možda će vam prvo pasti na pamet ideja da iskoristite agregatne funkcije MAX() ili MIN() i lako proverite da li su sve vrednosti iste, s obzirom da postoje samo dve moguće vrednosti.

Međutim, ako pokušate da izvršite komandu:

SELECT MAX(UBoji) FROM dbo.CrtaneEpizode;

ubrzo ćete udariti u zid pošto se javlja sledeća greška:

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for max operator.

SQL Server nudi veliki broj ugrađenih funkcija za rukovanje stringovima. Neke od njih su dobro poznate i često korišćene, dok su neke manje poznate ili su uvedene u novijim verzijama SQL servera. Predstavićemo sve string funkcije kroz nekoliko članaka. U prvom vas podsećamo na one najpoznatije i najkorišćenije.

SUBSTRING je funkcija koja kao rezultat vraća deo stringa koji prosledimo kao prvi parametar. Sintaksa je SUBSTRING(string, početak, dužina) gde prvi parametar string predstavlja bilo koji niz karaktera tipa char/varchar/text, nchar/nvarchar/ntext ili binary/varbinary/image. Tip podatka u kome ćemo dobiti rezultat zavisi od tipa ovog ulaznog parametra. Ukoliko ulazni parametar ima jedan od tipova iz prve grupe (char/varchar/text), rezultat je tipa varchar. Ako kao ulazni parametar prosledimo string tipa nchar/nvarchar/ntext, rezultat ćemo dobiti kao nvarchar, dok za binary/varbinary/image dobijamo varbinary. Drugi parametar označava poziciju u prvom parametru od koje počinje odsecanje karaktera, a treći parametar predstavlja dužinu željenog dela stringa koji smo prosledili kao prvi parametar.

Relacioni operator PIVOT u T-SQL-u se koristi za rotiranje tabele, tako što jedinstvene vrijednosti kolone transformiše u toliki broj kolona, pri čemu obavlja potrebne agregacije nad vrijednostima preostalih kolona. Jednostavnije rečeno, prevodi vertikalno orjentisanu tabelu u horizontalno orjentisanu tj. sa nivoa redova prelazi se na nivo kolona.
Često je skup vrijednosti kolone od koje kreiramo nove kolone konačan (ili želimo da prikažemo samo određene vrijednosti) i tada se, za taj skup, koristi PIVOT za predefinisani skup kolona.

Nekada je skup vrijednosti kolone koju želimo da pivotiramo po svim vrijednostima promljenljiv i tada se koristi dinamički pivot.

Primjer: Nad SQL Kefalo bazom kreirati upit koji kao rezultat daje broj epizoda za svaki crtać, po svim godinama u kojima su epizode proizvedene. Godine proizvodnje prikazati kao kolone.
Pošto je spisak godina proizvodnje promjenljiv, prvi korak je da se kreira taj spisak, koji će predstavljati spisak kolona u PIVOT-u. Ova konkatenacija stringa može da se uradi na više načina, kao što je opisano u prethodnim člancima:

Konkatenacija string vrednosti iz razlicitih redova (1. deo)
Konkatenacija string vrednosti iz razlicitih redova (2. deo)

Koristićemo primjer sa FOR XML PATH i STUFF funkcijom:

Nedavno smo objasnili kako se tabela ili rezultat nekog upita mogu pretvoriti u XML. Kao mala dopuna tog članka, danas ćemo prikazati i trik kako se svaki red tabele može pretvoriti u zaseban XML.

Već objašnjena sintaksa FOR XML sada se koristi unutar podupita, a kao uslov se dodaje JOIN tabele sa samom sobom:

SELECT 
	CrtacID
  , Naziv
  , Opis
  , (
		SELECT cfx.*
		FROM dbo.CrtaniFilmovi cfx
		WHERE cfx.CrtacID = cf.CrtacID
		FOR XML PATH ('')
	) AS CrtacXML
FROM dbo.CrtaniFilmovi cf;