Introducción a los archivos de formato
Un archivo que se usa para almacenar información de formato para cada campo de un archivo de datos de una tabla específica se denomina archivo de formato. Un archivo de formato proporciona toda la información de formato necesaria para la exportación o importación masiva de datos. El archivo de formato proporciona 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.
En Microsoft SQL Server 2000 y versiones anteriores, la exportación y la importación masivas funcionan con un solo tipo de archivo de formato. Esta función sigue siendo compatible. Sin embargo, SQL Server 2005 y versiones posteriores también son compatible con archivos de formato XML como alternativa. Los archivos de formato del tipo original se conocen como archivos de formato no XML.
Todos los archivos de formato contienen descripciones de todos los campos de un archivo de datos. Los archivos de formato XML también incluyen descripciones de las columnas de tabla correspondientes. Por lo general, los archivos de formato XML y no XML son intercambiables. Sin embargo, es recomendable usar la sintaxis XML para los nuevos archivos de formato porque proporciona varias ventajas con relación a los archivos de formato no XML. Un archivo de formato XML tiene las siguientes características:
Es autodescriptivo y fácil de leer, crear y ampliar.
Contiene los tipos de datos de las columnas de destino.
Esta característica permite la separación entre la representación de los datos en el archivo de datos y el tipo de datos asociado a cada campo del archivo. Por ejemplo, si un archivo de datos contiene una representación de caracteres de los datos, se perderá el tipo de columna SQL correspondiente.
El comando bcp y la instrucción BULK INSERT usan las columnas de la tabla de destino para realizar la conversión de tipos. Esto hace que sea necesaria una tabla de destino. Por contraste, la función OPENROWSET(BULK...) se basa en un archivo de formato XML para leer los datos desde un archivo de datos. Esto hace que una tabla de destino sea opcional.
Permite cargar un campo que contenga un único tipo de datos de objeto grande (LOB) desde un archivo de datos.
La sintaxis de un archivo de formato es independiente de la dirección de la operación; es decir, la sintaxis es la misma para exportaciones e importaciones masivas.
Para obtener información acerca del diseño de cada tipo de archivo de formato, vea la sección "Ejemplos" más adelante en este tema.
¿Cuándo se necesita un archivo de formato?
La instrucción INSERT ... La instrucción SELECT * FROM OPENROWSET(BULK...) necesita siempre un archivo de formato.
- Para bcp o BULK INSERT, en situaciones simples, el uso de un archivo de formato es opcional y pocas veces necesario. Sin embargo, en situaciones de importación masiva complejas, el archivo de formato suele ser necesario.
Los archivos de formato se necesitan cuando:
Se usa 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 usa 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.
Ejemplos
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 AdventureWorks2008R2. Esta tabla tiene cuatro columnas: EmployeeID, Name, Title y ModifiedDate.
Nota
Para obtener más información acerca de esta tabla y cómo crearla, vea Crear la tabla HumanResources.myTeam.
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 AdventureWorks2008R2.HumanResources.myTeam format nul -f myTeam.Fmt -n -T
The contents of this format file are as follows: 9.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, vea Descripción de los archivos de formato no XML.
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 AdventureWorks2008R2.HumanResources.myTeam format nul -f myTeam.Xml -x -n -T
El archivo de formato contiene:
<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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 Descripción de los archivos de formato XML.