Przenoszenie tabel pomiędzy bazami

Czasami zdarza się, że „uszkodziliśmy” jedną z tabel w bazie (np. usunęliśmy lub nadpisaliśmy ją jakimiś błędnymi danymi). Jedyny sposób na przywrócenie tabeli to odzyskanie całej bazy, a my tego nie chcemy bo potrzebujemy tylko tej jednej tabeli. Rozwiązaniem tego problemu jest odtworzenie naszej całej bazę pod inną nazwą, przekopiowanie odzyskanych danych z tabeli do naszej „uszkodzonej”, a następnie usunięcie odzyskanej bazy.

Załóżmy że usunęliśmy dane z tabeli Employees z bazy Northwind. Odzyskujemy z backupu bazę pod nazwą Northwind_Restore.

W przypadku usunięcia całej tabeli, aby przenieść dane można wydać polecenie :

SELECT *
INTO [Northwind].[dbo].[Employees]
FROM [Northwind_restore].[dbo].[Employees]

Utworzy ono nową tabelę i przeniesie dane do nowej tabeli. Jeśli jednak tabela miała np zastosowane Identiry, indeksy lub inne „skomplikowane” elementy to one się nie przeniosą. Czyli w sumie mamy kopię tabeli, ale nie do końca. 😉

Aby mieć identyczną kopię naszej usuniętej tabeli trzeba zastosować inną metodę.
Jeśli usunęliśmy część danych z tabeli to przed przenoszeniem danych należy usunąć z niej wszystkie dane (zastąpimy je nowymi odzyskanymi z backupu i będziemy mieli pewność, że się nie zdublowały)

TRUNCATE TABLE [Northwind].[dbo].[Employees]

Sprawdzamy czy dane się usunęły:

SELECT COUNT(*) FROM [Northwind].[dbo].[Employees]

Jeśli tak to powinno zwracać zero.

Jeśli usunęliśmy zupełnie naszą tabelę to przed przenoszeniem danych musimy ją ręcznie utworzyć (SQL sam za nas tego nie zrobi). Możemy zastosować opcję w Management Studio „Script Table As” a następnie „Create Table”.
Wygenerowany skrypt uruchamiamy na naszej bazie (tam gdzie usunęliśmy dane).

Mamy pustą tabelę. Teraz musimy wydać polecenie przenoszące dane z odzyskanej bazy i tabeli do nowoutworzonej pustej (lub wyczyszczonej) tabeli:

INSERT INTO [Northwind].[dbo].[Employees]
SELECT *
FROM [Northwind_Restore].[dbo].[Employees]

I tu mały problem:

Msg 8101, Level 16, State 1, Line 8
An explicit value for the identity column in table 'Northwind.dbo.Employees' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Okazuje się, że zostało zastosowane Identity w kolumnie [EmployeeID]. Aby zaczytać dane do tabeli należy zastosować polecenie SET IDENTITY_INSERT. Niestety w tym poleceniu należy wyszczególnić nazwy wszystkich kolumn (nie można zastosować "*")

Przykładowo:

SET IDENTITY_INSERT [Northwind].[dbo].[Employees] ON

INSERT INTO [Northwind].[dbo].[Employees](
[EmployeeID]
,[LastName]
,[FirstName]
,[Title]
,[TitleOfCourtesy]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
,[Extension]
,[Photo]
,[Notes]
,[ReportsTo]
,[PhotoPath]
)
SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[Title]
,[TitleOfCourtesy]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
,[Extension]
,[Photo]
,[Notes]
,[ReportsTo]
,[PhotoPath]
FROM [Northwind_Restore].[dbo].[Employees]

SET IDENTITY_INSERT [Northwind].[dbo].[Employees] OFF

(9 rows affected)

następnie możemy sprawdzić czy dane się przeniosły

select count(*) from [Northwind].[dbo].[Employees]

Dane zostały przeniesione.

Można też skorzystać z graficznego narzędzia dostarczonego przez Management Studio. Warunkiem bezbłędnego przeniesienia danych jest to aby w tabeli docelowej kolumny były tego samego typu co w docelowej. Nie można przenosić np kolumny z danymi INT do kolumny z danymi VARCHAR. Najlepszym sposobem aby dane przeniosły się prawidłowo jest usunięcie danych z tabeli (TRUNCATE) lub w przypadku usunięcia całej tabeli utworzenie jej skryptem z Management Studio (z indeksami i Identitami ;)).

Załóżmy że chcemy przenieść kilka kolumn z tabeli

SELECT TOP (5) [EmployeeID]
,[LastName]
,[FirstName]
,[Title]
--,[TitleOfCourtesy]
,[BirthDate]
--,[HireDate]
--,[Address]
,[City]
--,[Region]
--,[PostalCode]
--,[Country]
--,[HomePhone]
--,[Extension]
--,[Photo]
--,[Notes]
--,[ReportsTo]
--,[PhotoPath]
FROM [Northwind].[dbo].[Employees]

EmployeeID  LastName             FirstName  Title                          BirthDate               City
----------- -------------------- ---------- ------------------------------ ----------------------- ---------------
1           Davolio              Nancy      Sales Representative           1948-12-08 00:00:00.000 Seattle
2           Fuller               Andrew     Vice President, Sales          1952-02-19 00:00:00.000 Tacoma
3           Leverling            Janet      Sales Representative           1963-08-30 00:00:00.000 Kirkland
4           Peacock              Margaret   Sales Representative           1937-09-19 00:00:00.000 Redmond
5           Buchanan             Steven     Sales Manager                  1955-03-04 00:00:00.000 London

(5 rows affected)

W nowej docelowej tabeli będzie tylko kilka kolumn z tabeli źródłowej.
Tym razem w przykładzie utworzymy naszą tabelę pod nazwą Employees_NEW w bazie BAZA_TESTOWA.

USE [baza_testowa]
CREATE TABLE [dbo].[Employees_NEW]
(
[EmployeeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[BirthDate] [datetime] NULL,
[City] [nvarchar](15) NULL
)

Commands completed successfully.

Sprawdzamy

USE [baza_testowa]
SELECT * FROM [dbo].[Employees_NEW]

EmployeeID  LastName             FirstName  Title                          BirthDate               City
----------- -------------------- ---------- ------------------------------ ----------------------- ---------------

(0 rows affected)

uruchamiamy eksport danych.


Wybieramy bazę źródłową

i bazę docelową

Ustawiamy czy chcemy ręcznie utworzyć skrypt do przenoszenia danych czy uruchomić kopiowanie automatycznie

Ustawiamy z jakiej tabeli do jakiej mają być kopiowane dane. Możemy wybrać istniejącą tabelę ale również możemy zostawic taką jak jest wpisana (nazwa taka sama jak źródłowa) i wtedy zostanie on utworzona.

Następnie wciskamy przycisk MAPPING gdzie ustalimy jakie kolumny mają być kopiowane. Możemy tu też ustawić czy dane mają być najpierw usunięte z docelowej tabeli (DELETE ROWS...) czy mają być dopisane (APPEND ROWS...)

Potwierdzamy że ma kopiowanie ma być uruchomione natychmiast po zakończeniu konfiguracji. Można to też zapisać jako paczkę DTS (SSIS).

Uruchamiamy

No i skończyło się błędem.

Sprawdzamy przyczynę w logu.

Przyczyna leży w tym że pierwsza kolumna EmployeeID jest ustawiona jako IDENTITY czyli że w każdy wiersz będzie automatycznie wpisywany kolejny "numer wpisu".

Wracamy do MAPPINGS i tam zaznaczamy opcję ENABLE IDENTITY INSERT.

Puszczamy jeszcze raz export.

Teraz poszło wszystko prawidłowo. Sprawdzamy dane w tabeli

USE [baza_testowa]
SELECT * FROM [dbo].[Employees_NEW]

EmployeeID  LastName             FirstName  Title                          BirthDate               City
----------- -------------------- ---------- ------------------------------ ----------------------- ---------------
1           Davolio              Nancy      Sales Representative           1948-12-08 00:00:00.000 Seattle
2           Fuller               Andrew     Vice President, Sales          1952-02-19 00:00:00.000 Tacoma
3           Leverling            Janet      Sales Representative           1963-08-30 00:00:00.000 Kirkland
4           Peacock              Margaret   Sales Representative           1937-09-19 00:00:00.000 Redmond
5           Buchanan             Steven     Sales Manager                  1955-03-04 00:00:00.000 London
6           Suyama               Michael    Sales Representative           1963-07-02 00:00:00.000 London
7           King                 Robert     Sales Representative           1960-05-29 00:00:00.000 London
8           Callahan             Laura      Inside Sales Coordinator       1958-01-09 00:00:00.000 Seattle
9           Dodsworth            Anne       Sales Representative           1966-01-27 00:00:00.000 London

(9 rows affected)

No i wszystko jest OK.

Ten wpis został opublikowany w kategorii Microsoft SQL i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz