Uso de un archivo de formato para la importación en bloque de datos (SQL Server)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

En este artículo se describe cómo usar un archivo de formato en operaciones de importación masiva. Un archivo de formato asigna los campos del archivo de datos a las columnas de la tabla. Consulta Crear un archivo de formato (SQL Server) para información adicional.

Antes de empezar

  • Para que un archivo de formato trabaje con un archivo de datos de caracteres Unicode, todos los campos de entrada deben ser cadenas de texto Unicode (es decir, de tamaño fijo o cadenas Unicode terminadas en caracteres).
  • Para importar o exportar de forma masiva datos SQLXML, usa uno de los tipos de datos siguientes en el archivo de formato:
    • SQLCHAR o SQLVARCHAR (los datos se envían a la página de códigos del cliente o a la página de códigos implícita por la intercalación).
    • SQLNCHAR o SQLNVARCHAR (los datos se envían como Unicode).
    • SQLBINARY o SQLVARBIN (los datos se envían sin realizar ninguna conversión).
  • Azure SQL Database y Azure Synapse Analytics solo admiten bcp. Para información adicional, consulte:
  • Esta sintaxis, incluida la inserción masiva, no se admite en Azure Synapse Analytics. En Azure Synapse Analytics y otras integraciones de plataforma de base de datos en la nube, lleve a cabo el movimiento de datos con la instrucción COPY en Azure Data Factory o usando instrucciones T-SQL, como COPY INTO y PolyBase.

Condiciones de prueba de ejemplo

Los ejemplos de archivos de formato de este tema se basan en la tabla y archivo de datos definidos a continuación.

Tabla de ejemplo

El script siguiente crea una base de datos de prueba y una tabla llamada myFirstImport. Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

Archivo de datos de ejemplo

Mediante el Bloc de notas, cree un archivo vacío D:\BCP\myFirstImport.bcp e inserte los datos siguientes:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Como alternativa, puede ejecutar el siguiente script de PowerShell para crear y rellenar el archivo de datos:

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

Crear los archivos de formato

SQL Server admite dos tipos de archivos de formato: XML y no XML. El formato no XML es el formato original compatible con versiones anteriores de SQL Server.

Creación de un archivo de formato no XML

Revisa Archivos de formato no XML (SQL Server) para obtener información detallada. El siguiente comando hará uso de la utilidad BCP para generar un archivo de formato no XML, myFirstImport.fmt, basado en el esquema de myFirstImport. Si quiere usar un comando BCP para crear un archivo de formato, especifique el argumento format y use null en lugar de una ruta de acceso de archivo de datos. La opción format también requiere la opción -f . Además, en este ejemplo, el calificador c se usa para especificar los datos de caracteres, t, se usa para especificar una coma como terminador de campoy T se usa para especificar una conexión de confianza que usa seguridad integrada. En el símbolo del sistema, escriba el siguiente comando:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

El archivo de formato no XML, D:\BCP\myFirstImport.fmt debe tener el aspecto siguiente:

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

Importante

Asegúrese de que el archivo de formato no XML termina con un retorno de carro o avance de línea. De lo contrario, es probable que reciba el mensaje de error siguiente:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Creación de un archivo de formato XML

Consulta Archivos de formato XML (SQL Server) para información detallada. El siguiente comando hará uso de la utilidad bcp para crear un archivo de formato xml, myFirstImport.xml, basado en el esquema de myFirstImport. Si quiere usar un comando BCP para crear un archivo de formato, especifique el argumento format y use null en lugar de una ruta de acceso de archivo de datos. La opción format siempre requiere la opción -f y, para crear un archivo de formato XML, también debe especificar la opción -x . Además, en este ejemplo, el calificador c se usa para especificar los datos de caracteres, t, se usa para especificar una coma como terminador de campoy T se usa para especificar una conexión de confianza que usa seguridad integrada. En el símbolo del sistema, escriba el siguiente comando:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

El archivo de formato XML, D:\BCP\myFirstImport.xml debe tener el aspecto siguiente:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>

Uso de un archivo de formato para la importación en bloque de datos

Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato que se han creado anteriormente.

Uso de bcp y un archivo de formato no XML

En el símbolo del sistema, escriba el siguiente comando:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

Uso de bcp y un archivo de formato XML

En el símbolo del sistema, escriba el siguiente comando:

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

Uso de BULK INSERT y un archivo de formato no XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Uso de BULK INSERT y un archivo de formato XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Uso de OPENROWSET(BULK...) y archivo de formato no XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Uso de OPENROWSET(BULK...) y archivo de formato XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Más ejemplos

Crear un archivo de formato (SQL Server)
Usar un archivo de formato para omitir una columna de tabla (SQL Server)
Uso de un archivo de formato para omitir un campo de datos (SQL Server)
Uso de un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

Pasos siguientes