Jak uruchomić duży skrypt z wieloma wstawkami bez wyczerpania pamięci?

Pytanie:

Mam skrypt z około 45 tysiącami wstawek z wybranych instrukcji. Podczas próby uruchomienia pojawia się komunikat o błędzie informujący, że zabrakło mi pamięci. Jak mogę uruchomić ten skrypt?

Kontekst:

1. Dodano nowe pola danych, aby aplikacja działała przyjemnie z inną aplikacją, z której korzysta klient.
2. Dostałem arkusz kalkulacyjny danych od klienta pełnego danych, który zamapował bieżące elementy danych na wartości dla tych nowych pól.
3. Konwertowany arkusz kalkulacyjny do wstawiania instrukcji.
4. Jeśli uruchomię tylko niektóre instrukcje, działa, ale nie działa cały skrypt.
5. Nie. Nie ma literówek.

Jeśli istnieje inny sposób, powinienem ładować te dane, możesz mnie ukarać i dać mi znać.

Maksymalny rozmiar partii dla SQL Server 2005 wynosi 65 536 * Rozmiar pakietu sieciowego (NPS), gdzie NPS to zwykle 4KB. To działa do 256 MB. Oznaczałoby to, że każda instrukcja wstawiania miałaby średnio 5,8 KB. To nie wydaje się właściwe, ale może są tam obce przestrzenie lub coś niezwykłego.

Moją pierwszą sugestią byłoby umieszczenie instrukcji „GO” po każdej instrukcji INSERT. Spowoduje to rozbicie pojedynczej partii 45 000 instrukcji INSERT na 45 000 oddzielnych partii. Powinno to być łatwiejsze do strawienia. Bądź ostrożny, jeśli jedna z tych wkładek zawiedzie, możesz mieć trudności ze znalezieniem winowajcy. Możesz się zabezpieczyć przed transakcją. Możesz szybko dodać te instrukcje, jeśli twój edytor ma dobre wyszukiwanie i zamianę (co pozwoli ci wyszukiwać i zamieniać znaki powrotu, takie jak \ r \ n) lub funkcję makr.

Drugą sugestią jest użycie Kreatora do importowania danych bezpośrednio z Excela. Kreator buduje dla ciebie mały pakiet SSIS, a następnie uruchamia go. Nie będzie miał tego problemu.

BULK INSERTlub bcpwydają się bardziej odpowiednie opcje niż 45 000 instrukcji wstawiania.

Jeśli chcesz trzymać się instrukcji wstawiania, rozważę kilka opcji:

Odp .: Użyj transakcji i zawiń partie po 100 lub 500 lub 1000 wyciągów w każdym, aby zminimalizować wpływ na dziennik i partię. na przykład

BEGIN TRANSACTION;
INSERT dbo.table(a, ...) SELECT 1, ...
INSERT dbo.table(a, ...) SELECT 2, ...
...
INSERT dbo.table(a, ...) SELECT 500, ...
COMMIT TRANSACTION;
GO

BEGIN TRANSACTION;
INSERT dbo.table(a, ...) SELECT 1, ...
INSERT dbo.table(a, ...) SELECT 2, ...
...
INSERT dbo.table(a, ...) SELECT 500, ...
COMMIT TRANSACTION;
GO

B: Zamiast pojedynczych instrukcji wstawiania używaj jednocześnie UNION ALL100 lub 500 instrukcji, np

INSERT dbo.table(a, ...)
SELECT 1, ...
UNION ALL SELECT 2, ...
...
UNION ALL SELECT 500, ...
GO

INSERT dbo.table(a, ...)
SELECT 501, ...
UNION ALL SELECT 502, ...
...
UNION ALL SELECT 1000, ...
GO

Zostawiłem błąd dotyczący zwięzłości, ale chodzi o to, że nigdy nie próbowałbym wysłać pojedynczej partii 45 000 pojedynczych instrukcji do SQL Server.

Nie jestem pewien, dlaczego występuje błąd braku pamięci, ale istnieje łatwiejsze podejście.

Jeśli możesz wyeksportować dane z arkusza kalkulacyjnego do formatu rozdzielanego (np. Csv), możesz użyć kreatora importu danych w SSMS, aby wstawić dane:

Używając wielu SqlBulkCopy, utwórz tabelę tymczasową. Wstaw nowe dane do tabeli tymczasowej, a następnie scal dane z tabeli tymczasowej z istniejącą. Przykład z wykorzystaniem metody C # SqlBulkCopy.WriteToServer (DataTable) . Mam nadzieję, że to pomoże

Tak, moglibyśmy to zrobić, próbowałem z podejściem BCP (Bulk Copy Program), aby uniknąć problemu z OutOfMemory .

Uwaga : Wypróbowano na SQL Server 2014.

W BCP najpierw musimy wyeksportować dane źródłowej bazy danych do pliku bcp (w folderze katalogu lokalnego), a następnie zaimportować ten plik bcp do docelowej bazy danych.

Poniżej znajdują się kroki chodzenia po torcie:

Uwaga:

a) Upewnij się, że pusta tabela jest obecna w docelowej bazie danych

b) Upewnij się, że folder Temp znajduje się na dysku C.

1. Utwórz plik nietoperza o nazwie Export_Data.bat za pomocą polecenia pokazanego poniżej:

   bcp.exe [Source_DataBase_Name].[dbo].[TableName] OUT "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q 

   pauza

2. Uruchom ten plik nietoperza, w wyniku czego plik bcp zostanie wygenerowany w folderze Temp

3. Następnie utwórz kolejny plik nietoperza o nazwie Import_Data.bat za pomocą następującego polecenia:

   bcp.exe [Destination_DataBase_Name].[dbo].[TableName] IN "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q 

   Pauza

I zaczynamy!