We wcześniejszym rozdziale omówione zostało pobieranie danych za pomocą polecenia SELECT, używanie klauzuli WHERE (do ograniczania wierszy, które mają być zwrócone), korzystanie z funkcji liczbowych, łańcuchów znaków i daty. Przedstawiono też podsumowywanie zwracanych danych przy użyciu super aggregates GROUP BY, ROLLUP i COMPUTE. Na końcu pokazano pobieranie danych z więcej niż jednej tablicy poleceniem SELECT za pomocą operacji złączenia lub podzapytań.
Niniejszy rozdział skupia się na modyfikacji danych w tablicach SQL Servera przy użyciu poleceń INSERT, UPDATE i DELETE. Jednak, zanim zostaną omówione konkretne polecenia, należy się zapoznać z relacjami pomiędzy modyfikacjami danych a rejestracją zdarzeń w dzienniku transakcji.
SQL Server śledzi zmiany, zachodzące w bazie i rejestruje je w dzienniku transakcji. Zarządzanie dziennikiem transakcji zostało omówione w rozdziale 4. Modyfikując dane w bazie, należy mieć na uwadze, że każda zmiana zostaje zanotowana w dzienniku transakcji. Kiedy wykonywana jest operacja INSERT, kopia całego nowego wiersza jest zapisywana do dziennika transakcji; gdy uruchamia się polecenie DELETE, kopia całego usuwanego wiersza jest również tam zapisywana.
W przypadku operacji UPDATE zapisywanie w dzienniku nie jest takie oczywiste. Przy niektórych operacjach UPDATE SQL Server rejestruje jedynie bajty, które zostały zmienione. W wielu innych SQL Server musi utworzyć dwa wpisy w dzienniku: całej starej (usuniętej) wersji wiersza i całej nowej (wpisanej) jego wersji. Omawianie reguł warunkujących, w których przypadkach SQL Server rejestruje jedynie zmienione bajty, a w których zapisuje w dzienniku całe wiersze, jest poza zakresem tej książki. Ilość danych, które zostają zapisane w dzienniku transakcji, zależy również od liczby posiadanych indeksów – zagadnienie to zostanie omówione w rozdziale 13.
Od przedstawionych wymagań rejestrowania są wyjątki, takie jak narzędzie BCP (Bulk Copy Program) — zobacz rozdział 19., które jest odpowiednikiem wielu poleceń INSERT. Jeżeli zostaje uruchomiona szybka wersja BCP, indywidualne wiersze nie są zapisywane w dzienniku transakcji, gdy są wpisywane do tablicy; jednak, w przypadku tablic mających indeksy, każdy pojedynczy wiersz jest zapisywany w dzienniku transakcji. Jednak niektóre zapisy do tego dziennika dokonywane są w celu rejestracji przestrzeni zajmowanej podczas operacji BCP, bez względu na to, czy jest to „szybki” BCP, czy też nie. W rozdziale 10. została omówiona operacja SELECT INTO, która również nie jest rejestrowana (z wyjątkiem wymienionej wcześniej alokacji przestrzeni). Niniejszy rozdział omawia polecenie TRUNCATE TABLE, które jest odpowiednikiem DELETE bez rejestrowania każdego usuwanego wiersza.
Teraz, mając już pojęcie o przebiegu procesu rejestrowania transakcji, można przystąpić do zapoznania się z poleceniami, które wymuszają rejestrowanie zdarzeń. Na początku zostanie omówione polecenie INSERT.
Do dalszych ćwiczeń należy utworzyć kopie tablic publishers i sales. Posłużą one do odświeżenia tablic po wykonaniu na nich różnych działań. Należy włączyć opcję bazy danych Select Into/Bulk Copy, zanim zostaną uruchomione poniższe polecenia.
USE pubs
GO
EXEC sp_dboption 'pubs', 'Select into/bulkcopy', TRUE
SELECT * INTO tmpPublishers FROM publishers
SELECT * INTO tmpStores FROM stores
SELECT * INTO tmpTitles FROM titles
SELECT * INTO tmpSales FROM sales
EXEC sp_dboption 'pubs', 'Select into/bulkcopy', FALSE
Podstawowe polecenie INSERT dodaje w danym momencie jeden wiersz do tablicy, zmiennej tablicy lub poprzez widok do tablicy podstawowej. Za pomocą różnych kombinacji podstawowego polecenia INSERT można dodać wiele wierszy, stosując wybór danych z innej tablicy lub widoku albo uruchamiając procedurę składową lub funkcję. W każdym z tych przypadków należy znać strukturę tablicy, do której mają być wpisywane dane. Następujące informacje są bardzo przydatne:
· liczba kolumn w tablicy,
· typ danych każdej z kolumn,
· nazwy kolumn dla niektórych poleceń INSERT,
· więzy i własności kolumn takie, jak: identity, uniqueidentifier lub quantity > 20.
Więcej informacji na temat więzów i kolumn identity zostanie podanych w rozdziale 14.
Składnia polecenia INSERT jest następująca:
INSERT [INTO]
{table_or_view}
{{[ (column_list)]
VALUES
({DEFAULT |
constat_expression} [, ...n]) |
select_statement |
execute_statement} |
DEFAULT VALUES}
table_or_view ::=
{ table_name | view_name
|rowset_function
} [,...n]
Najprostsza metoda znalezienia liczby kolumn, wraz z ich nazwami i typem danych, to skorzystanie z okna Właściwości tablicy w Enterprise Managerze (zobacz rysunek 11.1).
Aby otworzyć to okno:
1. Wybierz bazę danych w folderze Databases.
2. Wybierz tablicę z folderu Tables.
3. Kliknij tablicę prawym klawiszem myszy i wybierz Właściwości.
W przypadku braku SQL Server Enterprise Managera można skorzystać z systemowej procedury składowej sp_help, aby uzyskać te same informacje:
go
EXEC sp_help publishers
Rysunek 11.1. Okno Właściwości tablicy
Część wyników powinna wyglądać podobnie do przedstawionych poniżej:
Column_name Type Computed Length
........... .......... ......... ............
pub_id char no 4
pub_name varchar no 40
city varchar no 20
state char no 2
country varchar no 30
Wyniki te pokazują nazwę kolumny, typ danych i pozycje każdej z kolumn. Informacje te są potrzebne przy budowaniu polecenia INSERT.
Do pozyskiwania informacji można również używać widoków INFORMATION_SCHEMA. Przykładowo, aby znaleźć nazwy kolumn i typy danych w poprzednim przykładzie, można uruchomić następujące zapytanie:
SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'publishers'
Najprostsza forma polecenia INSERT wymaga wartości dla każdej z kolumn tablicy w kolejności, w jakiej te kolumny zostały zdefiniowane. Kolejność była przedstawiona w poprzednich przykładach. Aby wpisać pojedynczy wiersz do tablicy tmpPublishers, należy uruchomić następujące polecenie:
INSERT INTO tmpPublishers
VALUES('9956', 'A New Publisher', 'Poulsbo', 'WA', 'USA')
Jeżeli użytkownik, który wykonuje to polecenie, ma uprawnienie INSERT do tej tablicy i nie narusza żadnych więzów, powinien otrzymać następujący komunikat SQL Servera:
(1 row(s) affected)
Ponieważ wszystkie kolumny są typu znakowego, wszelkie wpisywane wartości ujęte są w cudzysłów. Wartości liczbowe nie są ujmowane w cudzysłów.
Nieograniczone
Nowa wskazówka.
pola typu siatki danych, używane w komunikacyjnych narzędziach wizualnych, nie dodają automatycznie danych do bazy. Należy napisać odpowiedni kod do przejścia przez wszystkie wartości, zmienione w polach, a następnie wykorzystać polecenie INSERT/VALUES, aby umieścić te wiersze w bazie danych.
Najprostsza forma polecenia INSERT wymaga, aby użytkownik bezpośrednio podał wartość dla każdej z kolumn; każda wartość musi być podana we właściwej kolejności. Jeżeli mają one być podane w innej kolejności lub dla kolumny wartość nie ma być podawana explicite, należy skorzystać z innego wariantu polecenia INSERT.
Następujące polecenie INSERT ma listę nazw kolumn przed klauzulą VALUES; lista ta zawiera jedynie zbiór nazw kolumn w tablicy. Na liście VALUES powinny być jedynie wartości dla podanych kolumn.
INSERT INTO tmpPublishers(state, pub_id)
VALUES('AK', '9932')
Kiedy aplikacje typu klient, takie jak Microsoft Visual Basic lub PowerBuilder, łączą się z bazą danych, aby dodać dane, wykorzystują omówione właśnie polecenia INSERT/VALUES.
Co dzieje się z kolumnami, które nie zostały wymienione w liście kolumn? Jeżeli dodawany jest nowy wiersz, każda z kolumn musi mieć wartość. Jeżeli użytkownik nie ustalił wartości, SQL Server musi mieć możliwość samodzielnego jej określenia. Aby SQL Server mógł określić wartość, każda z kolumn, która nie była wymieniona na liście, musi spełniać jeden z poniższych warunków:
· Kolumna ma skojarzoną ze sobą wartość domyślną.
· Kolumna jest kolumną identity.
· Kolumna dopuszcza wartości NULL.
· Kolumna jest typu rowversion (formalnie nazywana timestamp w poprzednich wersjach SQL Servera).
Wartości domyślne i kolumny identity zostały omówione w rozdziale 14. Wartości NULL i kolumny rowversion zostały omówione w rozdziale 9.
W poprzednim poleceniu INSERT żadna wartość nie zostaje przypisana do kolumny pub_name, city i country. W tablicy publishers kolumny pub_name i city dopuszczają wartości NULL, natomiast kolumna country ma domyślną wartość USA. Po wykonaniu polecenia INSERT można uruchomić następujące zapytanie, aby sprawdzić, czy wiersz został dodany:
SELECT * FROM tmpPublishers
WHERE pub_id = '9932'
Warto zauważyć, że występują wartości NULL i wartość domyślna w kolumnie country:
Pub_id Pub_name City state country
....... ......... ...... ...... ........
9932 NULL NULL AK USA
Uruchomienie polecenia INSERT bez podania wartości kolumn, które nie spełniają ani jednego z podanych warunków, zakończy się komunikatem o błędzie.
Następujące polecenie INSERT nie podaje wartości dla kolumny pub_id:
INSERT INTO tmpPublishers(pub_name, city, state)
VALUES('The Best Books', 'New Orleans', 'LA')
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'pub_id', table 'pubs.dbo.tmpPublishers';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Warto zauważyć, że komunikat o błędzie nie jest całkowicie kompletny. Problemem nie jest to, że kolumna pub_id nie zezwala na wartości NULL, ale również to, że nie ma wartości domyślnej i nie jest kolumną rodzaju identity.
Można używać jeszcze innego rodzaju prostego polecenia INSERT, dotyczącego pojedynczego wiersza. Stosuje się go, gdy w poleceniu nie ma być listy nazw kolumn, a SQL Server ma skorzystać z wartości domyślnych, jeżeli istnieją (dotyczy to również wartości Null i identity). Można skorzystać ze słowa kluczowego DEFAULT w bieżącej liście wartości — ma to być sposób na poinformowanie SQL Servera, że ma zadecydować, jaką wartość wybrać w danej kolumnie. Warto rozważyć przykład:
VALUES('9950', DEFAULT, DEFAULT,'AK', DEFAULT)
Jeżeli każda z kolumn w tablicy ma jakiegoś rodzaju wartość domyślną, z której może skorzystać SQL Server, można użyć jeszcze jednej odmiany polecenia. Można bezpośrednio wskazać SQL Serverowi, aby użył wszystkich domyślnych wartości za pomocą słowa kluczowego DEFAULT VALUES, jak w poniższym wyrażeniu:
INSERT INTO tmpPublishers DEFAULT VALUES
We wszystkich poprzednich poleceniach INSERT wpisywano do tablicy pojedynczy wiersz. Gdy zachodzi potrzeba wpisania większej liczby wierszy, należy mieć źródło, w którym są już one zapisane. Takim źródłem danych jest na ogół inna tablica, złączenie dwóch lub więcej tablic albo widok. W tej formie polecenia INSERT używane jest podzapytanie, określające, ile wierszy danych zostanie wpisanych. (Podzapytania zostały omówione w rozdziale 10.). Wyniki podzapytania stają się zbiorem wierszy, który zostanie wpisany przez polecenie INSERT. Liczba kolumn w zbiorze wynikowym podzapytania musi być równa liczbie kolumn w tablicy oraz kolumny muszą mieć zgodne typy danych. W pierwszym przykładzie zostanie utworzona tablica do śledzenia adresów:
CREATE TABLE tblAddressList
(strName varchar(50) not null,
strAddress varchar(50) not null,
strCity varchar(20) not null,
strState char(2) )
This command completed successfully.
Tablica ta ma cztery pola znakowe, więc polecenie SELECT, wykorzystane do zapełnienia tablicy, musi zwrócić cztery kolumny znakowe. Oto przykład:
INSERT INTO tblAddressList
SELECT stor_name, stor_address, city, state
FROM tmpStores
(6 row(s) affected)
Inaczej niż w przypadku podzapytań, które zostały omówione wcześniej, podzapytanie, jakiego używa się w poleceniu INSERT, nie jest ujęte w nawiasy zwykłe.
Nazwy kolumn, używane w podzapytaniu, są ignorowane, tablica ma już nazwy kolumn, które są skojarzone z każdym polem.
Można uruchomić kolejne polecenie INSERT, aby dodać więcej wierszy do tablicy tblAddressList. Trzeba na przykład dodać nazwy i adresy z tablicy authors. Zamiast pojedynczej nazwy (nazwiska) tablica authors ma osobną kolumnę — imię (au_fname) i nazwisko (au_lname). W tablicy address_list powinna się znaleźć pojedyncza wartość dla kolumny name, należy więc połączyć imię i nazwisko w jedną wartość. W powstałym wynikowym łańcuchu można użyć przecinka i spacji.
...
b.senni