Archivos de formato para importar y exportar datos (SQL Server)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Al importar masivamente datos en una tabla de SQL Server o exportar masivamente datos de una tabla, puede usar un archivo de formato para almacenar toda la información de formato necesaria para exportar o importar datos masivamente. Esto incluye la información de formato para cada campo de un archivo de datos relativo a la tabla.

SQL Server admite dos tipos de archivos de formato: archivos de formato XML y no XML. Tanto los archivos con formato XML como los archivos no XML contienen descripciones de todos los campos de un archivo de datos, y los archivos de formato XML también contienen descripciones de las columnas de tabla correspondientes. Por lo general, los archivos de formato XML y no XML son intercambiables. Sin embargo, es recomendable utilizar la sintaxis XML para los nuevos archivos de formato porque proporciona varias ventajas con relación a los archivos de formato no XML. Para obtener más información, vea XML, archivos de formato (SQL Server).

Nota:

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.

Ventajas de los archivos de formato

Los archivos de formato proporcionan una manera flexible de escribir archivos de datos que requiere poca o ninguna modificación para adaptarlos a otros formatos de datos y para leer archivos de datos de otros programas.

Puedes realizar la importación masiva de datos sin tener que agregar o eliminar datos innecesarios o reordenar los datos existentes en el archivo de datos. Los archivos de formato pueden resultar especialmente útiles cuando existe una discrepancia entre los campos del archivo de datos y las columnas de la tabla.

Ejemplos de archivos de formato

Los siguientes ejemplos muestran el diseño de un archivo de formato no XML y de un archivo de formato XML. Estos archivos de formato corresponden a la tabla HumanResources.myTeam de la base de datos de ejemplo AdventureWorks2022 . Esta tabla tiene cuatro columnas: EmployeeID, Name, Titley ModifiedDate.

Nota:

Para obtener más información sobre la tabla y cómo crearla, consulta Tabla de ejemplo HumanResources.myTeam (SQL Server).

A Uso de un archivo de formato no XML

El siguiente archivo de formato no XML usa el formato de datos nativo de SQL Server para la tabla HumanResources.myTeam. Este formato se creó usando el siguiente comando bcp .

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

El comando bcp tiene como valor predeterminado una instancia local de SQL Server con autenticación de Windows. Puedes especificar otra instancia e información de inicio de sesión como desees. Para obtener más información, consulta bcp utilidad. Por ejemplo, para especificar un servidor remoto denominado instance con la autenticación de Windows, use:

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

El contenido de este archivo de formato es el siguiente, empezando por el número de versión principal de SQL Server y los metadatos de la tabla.

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

Para obtener más información, consulta Uso de archivos de formato no XML (SQL Server).

B. Uso de un archivo de formato XML

El siguiente archivo de formato XML usa el formato de datos nativo de SQL Server para la tabla HumanResources.myTeam. Este formato se creó usando el siguiente comando bcp .

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.xml -x -n -T

El archivo de formato contiene:

<?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="NativePrefix" LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Para obtener más información, vea XML, archivos de formato (SQL Server).

¿Cuándo se necesita un archivo de formato?

Normalmente, los archivos de formato son necesarios en las siguientes circunstancias:

  • Cuando se usa una instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...).

  • Para situaciones complejas de importación masiva mediante bcp o BULK INSERT.

  • Se utiliza el mismo archivo de datos como origen de varias tablas que tienen esquemas distintos.

  • El archivo de datos tiene un número de campos distinto al de columnas de la tabla de destino; por ejemplo:

    • La tabla de destino contiene como mínimo una columna para la que se ha definido un valor predeterminado o se admite un valor NULL.
    • Los usuarios no tienen permisos SELECT/INSERT en una o más columnas de la tabla.
    • Se utiliza un único archivo de datos con dos o más tablas que tienen esquemas distintos.

  • El orden de columnas del archivo de datos y de la tabla es distinto.

  • Los caracteres de terminación o las longitudes de prefijo no coinciden entre las columnas del archivo de datos.

Nota:

En caso de no disponer de ningún archivo de formato, si un comando bcp especifica un modificador de formato de datos (-n, -c, -w, o -N) o una operación BULK INSERT especifica la opción DATAFILETYPE, se usará el formato de datos especificado como método predeterminado para interpretar los campos del archivo de datos.

Contenido relacionado