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

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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. Consulte Creación de un archivo de formato con bcp (SQL Server) para obtener información adicional.

Prerrequisitos

• Para ejecutar comandos T-SQL en la instancia de SQL Server, use SQL Server Management Studio (SSMS), la extensión MSSQL para Visual Studio Code, sqlcmd o la herramienta de consulta T-SQL favorita.

• 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 exportar o importar de forma masiva Ejemplos de importación y exportación masiva de datos de documentos XML (SQL Server), use uno de los siguientes tipos de datos 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:

  • Carga de datos en Azure Synapse Analytics
  • Carga de datos de SQL Server en Azure Synapse Analytics (archivos planos)
  • Migración de los datos

• 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.

Cambie las ubicaciones de archivo local del ejemplo de código a una ubicación de archivo en el equipo.

Creación de un archivo de formato no XML

Revise Usar 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.

• Para usar un comando bcp para crear un archivo de formato, especifique el format argumento y use nul en lugar de una ruta de acceso de archivo de datos.
• La opción Format también requiere la opción -f.
• c se usa para especificar datos de caracteres
• t, se usa para especificar una coma como terminador de campo
• T se usa para especificar una conexión de confianza mediante la 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

Revise los archivos de formato XML (SQL Server) para obtener 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.

• Para usar un comando bcp para crear un archivo de formato, especifique el format argumento y use nul en lugar de una ruta de acceso de archivo de datos.
• La opción de formato siempre requiere la -f opción .
• Para crear un archivo de formato XML, también debe especificar la -x opción .
• c se usa para especificar datos de caracteres
• t, se usa para especificar una coma como terminador de campo
• T se usa para especificar una conexión de confianza mediante la 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.

Usar bcp y Usar archivos de formato no XML (SQL Server)

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"

Usar archivos de formato bcp y XML (SQL Server)

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;"

Usar BULK INSERT (Transact-SQL) y Usar archivos de formato no XML (SQL Server)

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;

Usar BULK INSERT (Transact-SQL) y archivos de formato XML (SQL Server)

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;

Usar OPENROWSET BULK (Transact-SQL) y Usar archivos de formato no XML (SQL Server)

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;

Usar OPENROWSET BULK (Transact-SQL) y archivos de formato XML (SQL Server)

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

• Creación de un archivo de formato con bcp (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)

Contenido relacionado

• bcp (utilidad)
• BULK INSERT (Transact-SQL)
• OPENROWSET BULK (Transact-SQL)
• Usar archivos de formato no XML (SQL Server)
• Archivos de formato XML (SQL Server)
• Archivos de formato para importar y exportar datos (SQL Server)