Talvolta è necessario esportare o importare una grande quantità di dati dentro o fuori un database. Queste operazioni sono chiamate bulk (cioè di massa) import ed export.
SQL Server fornisce degli strumenti per eseguire operazioni di import ed export massivi e copie di massa:
La utility bcp, la funzione OPENROWSET(BULK), il wizard di SQL Server per Import ed Export, l’istruzione BULK INSERT e l’istruzione SELECT INTO.
Questo articolo spiega come copiare grandi quantità di dati da una tabella o insieme di tabelle ad altri database usando questi strumenti ed usando ApexSQL Script ed ApexSQL Data Diff.
Copiare dati usando il SQL Server Import and Export Wizard
Il Wizard di Import and Export crea un pacchetto di Integration Services (SSIS) di cui si può pianificare l’esecuzione su determinate basi e che si può modificare usando SQL Server Data Tools.
Per lanciare il Wizard di Import and Export clicca con il desto sul database e seleziona il comando Task > Export data:
- Nella finestra Choose a Data source,collegati al database di origine. I seguenti permessi sono richiesti per usare il Wizard di Import and Export su un database di origine: di lettura dei dati dal database o file e di INSERT nel database msdb per salvare il pacchetto SSIS.
-
Nella finestra Choose a Destination, collegati al database di destinazione. I permessi richiesti per l’istanza di destinazione sono: scrittura dei dati sul database o file, creazione di un database o una tabella se necessario:
- Nella finestra Specify Table Copy or Query, scegli l’opzione Copy data from one or more tables or views.
-
Scegli le tabelle e viste e seleziona l’opzione Run immediately. nella finestra Complete Wizard:
Nota che il Wizard di Import and Export non tratterà una colonna identity in modo differente da altre colonne e perciò fallirà se si prova ad inserire i dati dentro ad una tabella che ha una colonna identity:
Se una tabella di origine ha un constraint su una chiave esterna, attenzione che il Wizard non carica le tabelle con uno specifico ordine per i constraint e quindi è possibile che una tabella contenente una chiave esterna venga caricata prima di una tabella contenente la chiave primaria referenziata, il che potrebbe causare un errore di vincolo di chiave esterna.
Tecniche per le copie massive
bcp utility
L’utility bcp è uno strumento a linea di comando che permette l’esportazione massiva di dati da un database di origine ad un file o l’importazione massiva di dati da un file in un database di destinazione.
Per esempio, per esportare i campi EmployeeID e Email nel file Emails.txt dalla tabella EmployeeEmail nel database TestDB, esegui il seguente comando bcp:
bcp TestDB.dbo.EmployeeEmail out Emails.txt –c -T
L’opzione –c specifica che l’utility è usata con dati di tipo testo e l’opzione –T dichiara una connessione sicura, indicando che l’utility bcp usa le credenziali di login dell’utente che è attualmente collegato. Se l’opzione –T non è specificata uno username ed una password devono essere indicati con le opzioni –U e –P.
La tabella deve esistere prima dell’importazione. Prima che i dati siano importati nella tabella di destinazione, la tabella nell’istanza di destinazione deve avere le colonne che corrispondono al tipo di dati da importare. Per inserire dati dal file Emails.txt nella tabella NewSchema.dbo.EmployeeEmail usa il seguente comando bpc:
bcp NewSchema.dbo.EmployeeEmail in Emails.txt -T –c
L’utility bcp può solamente esportare i dati in un file o importare da un file. Per poter usare l’utility i requisiti di sicurezza prevedono il permesso di effettuare SELECT sulle tabelle di origine.
Importare dati usando l’istruzione BULK INSERT
Per importare dati da un file specifico ad una tabella, si può usare l’istruzione BULK INSERT. Come per l’utility bcp, la tabella di destinazione deve esistere e deve avere i tipi di dati delle colonne che corrispondono ai dati da importare.
Specifica la tabella di destinazione e l’ubicazione del file dal quale importare i dati:
BULK INSERT TestRemote.dbo.EmployeeEmail FROM 'C:\Users\Lenovo\Desktop\Emails.txt'; GO
Importare dati usando la funzione OPENROWSET(BULK)
La funzione OPENROWSET(BULK) si connette ad una origine dati OLE DB per ripristinare i dati e permette l’accesso ai dati remoti attraverso il collegamento ad una origine dati remota.
INSERT … SELECT * FROM OPENROWSET(BULK…)
INSERT INTO AllEmails(Email) SELECT * FROM OPENROWSET( BULK 'C:\Users\Lenovo\Desktop\Emails.txt', SINGLE_BLOB) AS x;
La funzione OPENROWSET(BULK) fornisce una alternativa per accedere agli oggetti da un linked server ed è adatta per un importazione di dati estemporanea da una origine remota.
Importazione ed esportazione di dati usando SELECT INTO
Quando la clausola INTO è usata in combinazione con l’istruzione SELECT, permette la creazione di una nuova tabella basata sui risultati di una istruzione select. Per esempio, per copiare la tabella EmployeeEmail per copiare la tabella EmployeeEmail sulla stessa istanza nello schema di default del database TestDB, esegui la seguente query:
SELECT * INTO TestDB.dbo.EmployeeEmail FROM AdventureWorks2012.HumanResources.EmployeeEmail;
SELECT INTO non può essere usato per creare una nuova tabella su una istanza remota di SQL Server, ma una origine remota può essere inclusa nell’istruzione SELECT se c’è un link all’istanza remota.
Se la tabella di origine contiene constraint, indici e trigger, questi non verranno trasferiti nella nuova tabella. Le colonne nella tabella appena creata non erediteranno la proprietà IDENTITY dalla query se la l’istruzione SELECT contiene una funzione aggregata, la clausola JOIN, o la clausola GROUP BY e se la colonna identity è usata in una espressione, è usata più di una volta o viene da una origine dati remota.
Esportazione and importazione di dati usando ApexSQL Script
Usando ApexSQL Script, uno strumento per la migrazione dei database di SQL Server, si può preparare uno SQL script per esportare dati, o sia dati che strutture, da una istanza di origine. Lo script si esegue poi su una istanza di destinazione per importare i dati.
Per preparare lo script dei dati utilizzando ApexSQL Script lancia la finestra di dialogo New project, collegati al database ed a seconda della struttura dell’istanza di destinazione, imposta le opzioni del progetto. Assumendo che il database di destinazione esista, se si creano gli script sia per i dati che la tabella di origine, nel tab Structure script, togliere il check dalle opzioni Script CREATE DATABASE e SCRIPT USE for a database. Se si vuole creare lo script per una tabella che contiene constraints, indici, o chiavi esterne, selezionare l’opzione Script names per generare gli script con i nomi di questi oggetti:
Nel tab Data script, seleziona l’opzione Set IDENTITY_INSERT ON, per gestire il caricamento dei dati dentro tabelle che hanno colonne IDENTITY. Sotto l’opzione Rows to script seleziona Script rows as INSERT:
Se il database di destinazione ha una struttura differente dal database di origine, nel tab Schema mapping, seleziona l’opzione Schema mapping, seleziona l’opzione Exclude schemas per escludere gli schema dai nomi degli oggetti e clicca il bottone Open:
Nella finestra Results grid, sotto la Data grid, filtra le colonne dalle quali estrarre i dati:
Dalla Structure grid, seleziona le tabelle per le quali creare gli script e lancia lo Script wizard:
Nello Script wizard, seleziona il modo di scripting e clicca il bottone Next:
Salva lo script ed eseguilo sulla stessa istanza o su una remota:
Copiare dati usando ApexSQL Data Diff
Se una tabella di destinazione già esiste ed ha la stessa struttura della tabella di origine, puoi usare ApexSQL Data Diff, uno strumento di comparazione dati per copiare i dati da una o più tabelle di origine ad un database di destinazione.
Lancia un nuovo progetto, collegati ad un database di origine ed uno di destinazione e clicca il bottone Compare:
Nella Results grid, seleziona le tabelle da copiare e premi il bottone Synchronize:
Nel Synchronization wizard si può vedere l’effetto degli script di sincronizzazione e verificare tutti gli avvisi e le opzioni:
Clicca il bottone Create script ed esegui lo script:
Risorse utili:
bcp Utility
SQL Server Import and Export Wizard
Bulk Import and Export of Data (SQL Server)
July 25, 2017
