Compartir a través de

Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

  • Artículo

Un archivo de datos puede contener campos organizados en un orden diferente al de las columnas correspondientes en la tabla. Este tema trata los archivos de formato XML y no XML que se han modificado para alojar un archivo de datos cuyos campos están organizados en un orden diferente al de las columnas de la tabla. El archivo de formato modificado asigna los campos de datos a las columnas correspondientes de la tabla.

[!NOTA]

Se puede usar un archivo de formato XML o no XML para realizar una importación masiva de un archivo de datos en la tabla mediante un comando de bcp, la instrucción BULK INSERT o la instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...). Para obtener más información, vea Usar un archivo de formato para importar datos de forma masiva (SQL Server).

Tabla y archivo de datos de ejemplo

Los ejemplos de archivos de formato modificados de este tema se basan en la siguiente tabla y archivo de datos.

Tabla de ejemplo

Los ejemplos de este tema requieren la creación de una tabla denominada myTestOrder en la base de datos de ejemplo AdventureWorks2012, bajo el esquema dbo. Para crear esta tabla, en el Editor de consultas de SQL Server Management Studio, ejecute el código siguiente:

USE AdventureWorks2012;
GO
CREATE TABLE myTestOrder 
   (
   Col1 smallint,
   Col2 nvarchar(50) ,
   Col3 nvarchar(50) , 
   Col4 nvarchar(50) 
   );
GO

Archivo de datos

El archivo de datos, myTestOrder-c.txt, contiene los siguientes registros:

DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4
DataField3,DataField2,1,DataField4

Para importar masivamente datos desde myTestSkipCol2-c.dat a la tabla myTestSkipCol, el archivo de formato debe asignar el primer campo de datos a Col3, el segundo campo de datos a Col2, el tercer campo de datos a Col1 y el cuarto campo de datos a Col4.

Usar un archivo de formato no XML

Puede cambiar el orden de asignación de una columna cambiando el valor de orden de la columna para indicar la posición del campo de datos correspondiente.

El siguiente archivo de formato no XML de ejemplo presenta un archivo de formato, myTestOrder.fmt, que asigna los campos de myTestOrder-c.txt a las columnas de la tabla myTestOrder. Para obtener información acerca de cómo crear el archivo de datos y la tabla, vea "Tabla y archivo de datos de ejemplo", anteriormente en este tema. El archivo de formato utiliza el formato de datos de carácter.

El archivo de formato contiene la siguiente información:

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

[!NOTA]

Para obtener más información acerca del diseño de archivos de formato no XML, vea Archivos de formato no XML [SQL Server].

Ejemplo

El siguiente ejemplo utiliza una instrucción BULK INSERT para realizar una importación masiva de datos del archivo de datos myTestOrder-c.txt a la tabla de ejemplo myTestOrder utilizando el archivo de formato no XML myTestOrder.fmt.

En el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks2012;
GO
BULK INSERT myTestOrder
FROM 'C:\myTestOrder-c.txt' 
WITH (formatfile='C:\myTestOrder.fmt');
GO

Usar un archivo de formato XML

El siguiente archivo de formato no XML de ejemplo muestra un archivo de formato, myTestOrder.xml, que asigna los campos de myTestOrder-c.txt a las columnas de la tabla myTestOrder. Para obtener información sobre la creación del archivo y de la tabla de datos, vea "Tabla y archivo de datos de ejemplo", anteriormente en este tema.

El archivo de formato myTestOrder.xml contiene la siguiente información:

<?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="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="3" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="1" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

[!NOTA]

Para obtener información acerca de la sintaxis del esquema XML y ejemplos adicionales de archivos de formato XML, vea XML, archivos de formato (SQL Server).

Ejemplo

El siguiente ejemplo utiliza el proveedor de conjuntos de filas BULK OPENROWSET para importar datos del archivo de datos myTestOrder-c.txt a la tabla de ejemplo myTestOrder utilizando el archivo de formato XML myTestOrder.xml. La instrucción INSERT… SELECT especifica la lista de columnas en la lista de selección.

En el Editor de consultas de SQL Server Management Studio, ejecute el siguiente código:

USE AdventureWorks2012;
GO
INSERT INTO myTestOrder 
  SELECT Col1, Col2, Col3, Col4
      FROM  OPENROWSET(BULK  'C:\myTestOrder-c.txt',
      FORMATFILE='C:\myTestOrder.Xml'  
       ) AS t1;
GO

Vea también

Conceptos

Usar un archivo de formato para omitir una columna de tabla (SQL Server)

Usar un archivo de formato para omitir un campo de datos (SQL Server)