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).

Kada u SSIS paketima želimo da izvršimo određeni skup radnji za više različitih entiteta ili djelova entiteta, nije uvijek neophodno da se pravi više paketa ili control flow-ova. Korišćenjem komponente Foreach Loop Container može da se simulira izvršavanje while petlje u programskim jezicima. Primjeri korišćenja mogu da budu prebacivanje podataka iz različitih fajlova u SQL bazu ili obrnuto, arhiviranje podataka neke tabele po različitim kriterijumima i slično.

Dodavanje varijabli

Za prolazak kroz petlju potrebne su nam varijable, jedna koja sadrži kolekciju za koju se petlja izvršava koja je tipa Object i zvaćemo je Loop_Count, kao i varijable za datum i tip u kojima se čuva pokazivač na tekući član tj. za njihove različite vrijednosti prolazimo kroz petlju.

Kreiranje control flow-a

Prvo dodajemo Execution task komponentu u kojoj ćemo definisati skup datuma i tipova za koje će se izvršavati petlja. Izvor može biti sama tabela iz koje se presipaju podaci, a može biti i neka tabela metapodataka. Kreirajmo tabelu metapodataka i napunimo je sa 3 različite vrijednosti.

CREATE TABLE dbo.LoopPodaci
 (Datum INT,
  Tip tinyint,
  ValidInd bit);
GO
INSERT INTO dbo.LoopPodaci (Datum,Tip,ValidInd)
VALUES
	(20140630,1,1),
	(20140731,2,1),
	(20140831,2,1);

U tabeli postoji i kolona ValidInd koja predstavlja indikator koji određuje da li će se slog uzeti u obzir. Na osnovu navedenog, upit za Execution task komponentu je sledeći:

SELECT Datum,Tip
FROM dbo.LoopPodaci
WHERE ValidInd = 1;

Nakon definisanja izvora i podešavanja ResultSet-a na Full result set, potrebno je da se pozicioniramo na Result set granu u drvetu sa lijeve strane i dobijeni rezultat dodijelimo promjenljivoj tipa Object koju smo prethodno kreirali:

Zatim prevučemo komponentu Foreach Loop Container i povežemo sa kreiranom Execution task komponentom. Pozicioniramo se na Foreach Loop Container, desni klik miša i izaberemo opciju Edit. U Collection grani sa lijeve strane za Enumerator izaberemo Foreach ADO Enumerator, pod ADO object source variable izaberemo Loop_Count varijablu i opciju Rows in the first table:

Nakon toga na grani Variable Mappings izaberemo prvo varijablu Datum a potom Tip i one će automatski dobiti Index 0 odnosno 1, respektabilno. Ovdje je bitno obratiti pažnju da redosled izbora varijabli zavisi od redosleda koji je definisan u rezultatu koji je dodijeljen promjenljivoj tipa Object koja im predstavlja izvor, kako bi dodjela bila ispravna:

Ovim smo obezbijedili da kombinacija promjenljivih Datum i Tip dobije onoliko vrijednosti koliko ih ima u izvornom upitu i za njih možemo da definišemo bilo koje akcije u okviru Foreach Loop Container komponente. U našem primjeru kreiran je paket u kome se podaci prebacuju iz jedne tabele u drugu po datumu i tipu i nakon toga se za svaki datum i tip izvrši određena procedura.

U ovom članku nećemo opisivati akcije i način njihovog kreiranja, dovoljno je da znate da je dozvoljeno da koristite sve komponente i operacije koje su inače dozvoljene u control flow-u.

Kao skoro svaki program danas, i SQL Server Managment Studio nudi opciju da se razne operacije mogu pozivati prečicama sa tastature. Mnoge od njih verovatno i koristite svakodnevno, ali ono što ćemo opisati u ovom članku je posebna vrsta prečica koje nisu toliko poznate, a zovu se Query Shortcuts. U pitanju su prečice koje umesto okidanja definisanih komandi, izvršavaju unapred pripremljene upite. Ako dodate prave upite i malo se naviknete, ove prečice mogu značajno da vam olakšaju svakodnevni rad.

Za podešavanje, otvorite iz menija Tools -> Options, a zatim, u otvorenom prozoru sa leve strane Environment -> Keyboard -> Query Shortcuts.

Neke od ovih prečica su već predefinisane u SSMS, ali možete i sami da ih promenite ili

Često se javlja potreba da se prilikom upisivanja podataka u neku tabelu o tome obaveste određeni korisnici ili da se to negde zabeleži, zajedno sa vremenom kad je podatak upisan i korisnikom koji je izvršio upisivanje. Najpraktičniji način da se ovo uradi je upotreba trigera.

Za ovu priliku, kreiraćemo tabelu u koju će biti upisani pomenuti podaci prilkom svakog dodavanja novog reda u tabelu CrtaniFilmovi u našoj bazi SQL_KEFALO:

CREATE TABLE log_insert
(id int IDENTITY,
CrtacID int,
Naziv nvarchar(50),
Korisnik nvarchar(50),
Datum datetime DEFAULT(GETDATE()));

Zatim na tabeli CrtaniFilmovi kreiramo After Insert triger koji će u kreiranu tablu logovati crtani film koji je dodat u tabelu, kao i naziv korisnika koji je dodao taj novi red u tabelu i vreme dodavanja:

CREATE TRIGGER trg_LogInsert ON CrtaniFilmovi
AFTER INSERT
AS
BEGIN
	INSERT INTO log_insert (CrtacID, Naziv, Korisnik)
	SELECT CrtacId, Naziv, SUSER_SNAME() 
        FROM inserted;
END

Sada možemo da probamo da li kreirani triger radi tako što ćemo dodati novi red u tabelu CrtaniFilmovi, a onda proveriti tabelu log_insert.

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.

Promjenljive ili varijable u SSIS paketima služe za čuvanje vrijednosti koje im se dodjeljuju i koje se prosleđuju tokom izvršavanja paketa. Nekada je potrebno da znamo te vrijednosti kako bi se izvršilo detektovanje grešaka prilikom izvršavanja, da bi se utvrdilo da li je do greške došlo zbog pogrešne dodjele ili prosleđivanja vrijednosti.

Vrijednosti promjenljivih tokom izvršavanja možete vidjeti kroz nekoliko koraka:

• Postavite breakpoint na komponenti na kojoj želite da vidite vrijednost promjenljivih tako što se pozicionirate na komponentu, desni klik miša i izaberete opciju Edit Breakpoints:



• U sledećem prozoru izaberite OnPreExecute i OnPostExecute event opcije, kako biste vidjeli

Do sada smo u dva članka (String funkcije (1. deo) i String funkcije (2. deo)) opisali većinu funkcija za rukovanje stringovima koje SQL server nudi. U ovom članku, koji ujedno predstavlja i poslednji u nizu članaka o string funkcijama, opisane su neke manje poznate funkcije, koje uprkos tome mogu biti veoma korisne.

Funkcija PATINDEX je veoma slična funkciji CHARINDEX i omogućava nam da pronađemo poziciju jednog stringa u okviru drugog stringa. Unapređenje PATINDEX-a u odnosu na CHARINDEX je u tome što string čiju poziciju tražimo u okviru većeg stringa može sadržati specijalne karaktere kao što su % i _, pa bi se moglo reći da funkcioniše veoma slično funkciji za poređenje LIKE, s tim što kao rezultat vraća poziciju stringa (paterna) u okviru drugog stringa. Sintaksa za upotrebu ove funkcije je PATINDEX(%patern koji se traži%, string koji se pretražuje). Prvi parametar je karakter ili niz karaktera čiju poziciju tražimo u drugom parametru. Potrebno je staviti procente na početku i na kraju stringa koji tražimo, osim ako smo sigurni da želimo da ga nađemo samo na početku ili kraju drugog stringa. U tom slučaju se procenat na početku, odnosno na kraju, može izostaviti. Procenat (%) ili donja crta (_), kao i ostali specijalni karakteri koji se koristi sa LIKE funkcijom, se mogu upotrebiti u srednjem delu prvog parametra. Rezultat poziva funkcije je celobrojna vrednost koja predstavlja poziciju traženog paterna u okviru stringa koji se pretražuje.
Upiti:

SQL Server backup i restore operacije se dešavaju unutar konteksta recovery modela baze. Recovery model je podešavanje baze koje kontroliše kako se loguju transakcije za odgovarajuću bazu, da li je moguće backup-ovati transakcioni log i koji tipovi restorovanja baze su mogući. Postoje 3 tipa recovery modela:

Prost (Simple) recovery model – Osnovni recovery model, transakcije se automatski brišu prilikom završetka transakcije i nije podržana nijedna operacija koja zahteva backup-ovanje transakcionog loga. Moguće je izvršavati full i diferencijalni backup. U slučaju bilo kakvog pada sistema sve promene na bazi koje su se desile od poslednjeg backup-a se moraju ponovo izvršiti. Sledeće funkcionalnosti SQL Servera nije moguće koristiti ukoliko je baza u ovom recovery modelu: