Wypełniamy bazę danych testowymi danymi

Witam, dzisiaj opiszę jak w łatwy i szybki sposób wypełnić pustą strukturę bazy danych testowymi danymi. Do tego celu użyję narzędzia MS Excel. Na początku opiszę jak wygenerować odpowiednie dane a później pokażę jak stworzyć do nich zapytanie którym "wrzucimy" dane do bazy. Generowanie danych do bazy pokażę na przykładzie bazy "Biblioteka" której proces tworzenia opisałem >>>tutaj<<<

Dane będziemy "wrzucać" do bazy "Biblioteka" na środowisku SQL SERVER, w aplikacji Management Studio.


Generujemy dane do tabeli KATEGORIE

Krok 1. Otwieramy program MS Excel.

Krok 2. W Internecie wyszukujemy sobie wykaz kategorii książek. Chodzi o jakikolwiek wykaz kategorii książkowych z jakiejkolwiek strony i wklejamy go do jednego z arkuszów Excela do pierwszej kolumny A.

generowanie danych do testowej bazy danych

Krok 3. Teraz przechodzimy do kolumny B. W pierwszych dwóch komórkach wpisujemy wartości 1 i 2. Zaznaczamy te oba pola i dwukrotnie klikamy prawym przyciskiem myszy na czarnym kwadraciku w prawym dolnym rogu zakresu który zaznaczyliśmy.

generowanie danych do testowej bazy danych

dwukrotne kliknięcie spowoduje automatyczne wypełnienie poniższych komórek kolejnymi wartościami aż do wiersza 26.

generowanie danych do testowej bazy danych

ta nowa kolumna posłuży nam jako wartości pola "id_kategoria"

Krok 4. Teraz ustawiamy się w komórce C1 i wpisujemy formułę

="insert into Kategorie values("&TEKST(B1;"0")&",'"&A1&"');"

Po kliknięciu w enter (zatwierdzeniu formuły) w komórce gdzie wpisaliśmy naszą formułę powinniśmy uzyskać wartość (poniżej wytłumaszę bardziej szczegółowo formułę)

insert into Kategorie values(1,'Akcja');

widzimy, że otrzymaliśmy gotowy kod do wrzucenia do bazy do tabeli "Kategorie" jednego rekordu. Ustawiamy się w komórce C1 i znowu dwukrotnie klikemy na czarny kwadracić.

generowanie danych do testowej bazy danych

w tym momencie Excel automatycznie wypełni kolumnę danymi aż do wiersza 26 kompiując naszą formułę do wszyskich poniższych komórek.

generowanie danych do testowej bazy danych

teraz wystarczy skopiować zaznaczony powyżej zakres komórek i wkleić do Management Studio i uruchomić kod.

Zatrzymajmy się jednak przy formule z początku kroku 4
="insert into Kategorie values("  &  TEKST(B1;"0")  &  ",'"  &  A1  &  "');"

jest to po prostu złączenie kilku tekstów w jedną całość za pomocą łącznika "&".  Powyżej przed i po łączniku dałem po dwie spacje żebyście wizualnie zobaczyli poszczególne ciągi znaków. Każdy pojedynczy ciąg znaków umieszczamy w podwójnych cudzysłowach " " (chyba, że dołączamy komórkę której typ tekst wtedy wpisujemy tylko adres tej komórki np. &A1) i po prostu łączymy je w jedną całość. Jednak w naszej formule musieliśmy posłużyć się funkcją TEKST() która pomogła nam zamienić wartości liczbowe z kolumny B w tekst, bo nie możemy łączyć różnych typów danych i wyświetlać ich w jednej komórce.


Generujemy dane do tabeli AUTORZY

Krok 1. Uruchamiamy MS Excel

Krok 2. Pierwszą kolumnę A zostawimy na formułę końcową. Ustawiamy się więc w komórce B1 w wpisujemy tam wartość 1 a w komórce B2 wpisujemy wartość 2. Zaznaczamy obie komórki i przeciągamy (wciskamy PPM i trzymamy) czarny kwadracik (prawy dolny róg) w dół do wiersza np.100.

generowanie danych do testowej bazy danych

generowanie danych do testowej bazy danych

Krok 3. Przechodzimy do arkusza nr 2 i kopiujemy do pierwszej kolumny imiona osób. Ja w Internecie wyszukałem wykaz imion żeńskich i męskich i skopiowałem dane z tego wykazu do drugiego arkusza. Zmieniamy nazwę drugiego arkusza na "imiona".Po skopiowaniu danych dane z wykazu imion wypełniły mi zakres danych od A1 do A334

generowanie danych do testowej bazy danych

Krok 4. Teraz przechodzimy do trzeciego arkusza i do pierwszej kolumny wprowadzamy duże litery alfaberu. Zmieniamy jego nazwę na "alfabet"

generowanie danych do testowej bazy danych

Krok 5. Wracamy do pierwszego arkusza i klikamy w komórkę A1, gdzie wprowadzimy formułę. Naszą formułę rozpoczynamy od kodu
="insert into Autorzy values("

Krok 6. teraz dodamy do naszej formuły część która stworzy dane do pola "id_autor"
&TEKST(B1,"0")

powyższy kod zamieni nam liczbę z kolumny B1 na tekst.

Krok 7. Teraz wygenerujmy kod który wypełni nam pole "imie" w tabeli Autorzy. do naszej formuły dodajemy
&",'"

bo pole id_autro jest liczbą więc po tej wartości musi stać przecinek a kolejne pole będzie imienie które jest tekstem więc musimy dać znak '. Teraz możemy dodać kod dotyczący samego imienia. Pamiętajmy, że w drugim arkuszu w pierwszej kolumnie mamy wstawione imiona od komórki A1 do A334. Jak chcę zrobić tak, żeby w każdym wierszu (w pierwszym arkuszu) w którym wykona się nasza formuła Excel automatycznie wylosował jakieś imię z arkusza "imie" i wstawił do arkusza pierwszego. Kawałek kodu który dodamy do naszej formuły będzie wyglądał tak.
&INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))

funkcja INDEKS ma trzy parametry INDEKS(tablica ; numer_wiersza ; [numer_kolumny])
w pierwszym parametrze wskazujemy zakres komórek z którego będziemy losowali wartość imiona!$A$1:imiona!$A$334 . Zwróć uwagę, że przy adresach komórek zastosowałem znak $ który mówi o tym, że przy kopiowaniu formuły z jednej do drugiej komórki, zakres komórek z arkusza "imiona" będzie stały i nie będzie się przesuwał wraz z kopiowaniem tej formuły do innych komórek.
w drugim parametrze podajemy numer wiersza z którego będziemy chcieli "skopiować" wartość. W naszym przypadku chcemy aby za każdym razem Excel wziął wartość (imię) z losowaj komórki więc musimy zastosować funkcję LOS.ZAKR(od;do). Funkcja ta za każdym jej wywołaniem zwróci nam losową liczbę z podanego zakresu np. LOS.ZAKR(1;30) zwróci nam liczbę z zakresu od 1 do 30.
trzeci parametr numer kolumny jest opcjonalny. Z uwagi na to, że w arkuszu imiona mamy tylko jedną kolumnę to ten parametr możemy pominąć lub wstawić tam wartość 1.

Krok 8. Przejdźmy do wygenerowania nazwisk. Przydałoby się żeby nazwiska nie były realne (ochrona danych osobowych) więc pokażę Wam jak w łatwy sposób wygenerować fikcyjne dane do pola nazwisko. Do naszych celów wykorzystamy trzeci arkusz "alfabet".

generowanie danych do testowej bazy danych

nasze nazwisko będzie się składało z pierwszych trzech znaków imienia i trzech kolejnych losowo wybranych znaków z alfabetu z arkusza "alfabet". Wracamy do pierwszego arkusza i do naszej formuły dodajemy kod. Założyłem, że nazwiska będę podawał dużymi literami.
&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

no tutaj już robi się bardziej skomplikowanie ale po kolei
na początku skopiujemy 3 znaki od lewej z imienia i zamienimy to na duże litery. Do tego celu muszę wykorzystać trzy funkcje: INDEKS(), LEWY() i LITERY.WIELKIE()

na początek wyciągniemy trzy znaki z losowo wybranego imienia z arkusza "imiona".
INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))

funkcji INDEKS() już chyba nie trzeba tłumaczyć bo wytłumaczyłem to wcześniej. Mamy więc pobrane losowe imię ale teraz musimy wyciąć z niego tylko 3 znaki od lewej. Wykorzystamy do tego funkcję LEWY() w której podajemy dwa parametry LEWY(tekst ; ilość_znaków_do_wycięcia). W naszym przypadki jako pierwszy parametr podamy cały kod funkcji INDEX, czyli INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334)) a jako drugi parametr podamy cyfrę 3, bo chcemy wyciąć trzy znaki. Nasz kod przybierze postać.
LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3)

teraz wystarczy zamienić otrzymany ciąg znaków na duże litery za pomocą funkcji LITERY.WIELKIE() gdzie jako parametr podajemy tylko tekst który chcemy zamienić na duże litery. W naszym przypadku będzie to kod który otrzymaliśmy w poprzednim kroku czyli LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3), więc nasz nowy kod będzie wyglądał tak.
LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))

Teraz wystarczy dołączyć do naszego kodu trzy losowo wybrane znaki z arkusza "alfabet". Pojedynczy znak wyciągamy następującym kodem.
INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

żeby mieć trzy losowe znaki musimy powtórzyć ten kod trzykrotnie ze znakiem & pomiędzy nimi. Cały nasz kod generujący trzy dowolne znaki będzie wyglądał więc następująco
INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

teraz złączamy wszystkie elementy razem i otrzymujemy
&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

ok mamy już nazwisko. Teraz wystarczy tylko zakończyć formułę i złączyć wszystkie jej części w całość. Kod całej formułygenerującej całe zapytanie będzie wyglądał następująco.
="insert into Autorzy  values("&TEKST(B1;"0")&",'"&INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))&"','"&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&"');"

powyższa formuła w każdym wierszu wygeneruje nam tekst w strukturze jak poniżej.
insert into Autorzy values(1,'Wiktoria','KRYJWB');

teraz wystarczy tylko skopiować formułę w dół więc ustawiamy się pierwszej komórce (A1) w pierwszym arkuszu i klikamy dwukrotnie na czarny kwadracik w prawym dolnym rogu komórki.

generowanie danych do testowej bazy danych

Excel automatycznie skopiuje formułę dół aż do komórki A100, bo tyle wierszy "stworzyliśmy" w kroku 1.

generowanie danych do testowej bazy danych


z pomoca funkcji opisanych powyżej jesteście w stanie wygenerować dane do każdej z tabel bazy "Biblioteka"

Bazę 'Bibliteka' z wygenerowanymi testowymi danymi możesz pobrać >>>tutaj<<<