Usar un archivo de formato para importar datos de forma masiva

Artículo
12/15/2008

En este tema se describe cómo usar un archivo de formato en operaciones de importación masiva. El archivo de formato asigna los campos del archivo de datos a las columnas de la tabla. En Microsoft SQL Server 2005, puede utilizar un archivo de formato XML o no XML para importar datos de forma masiva al utilizar un comando bcp o un comando Transact-SQL BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...).

Importante:
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).

[!NOTA] Si no está familiarizado con los archivos de formato, vea Descripción de los archivos de formato no XML y Descripción de los archivos de formato XML.

Opciones de los archivos de formato para los comandos de importación masiva

La siguiente tabla resume la opción de archivo de formato para cada uno de los comandos de importación masiva.

Comando de carga masiva	Opción de archivo de formato
BULK INSERT	FORMATFILE = 'format_file_path'
INSERT ... SELECT * FROM OPENROWSET(BULK...)	FORMATFILE = 'format_file_path'
bcp … in	-fformat_file

Para obtener más información, vea bcp (utilidad), BULK INSERT (Transact-SQL) o OPENROWSET (Transact-SQL).

[!NOTA] Para importar o exportar datos SQLXML de manera masiva, utilice uno de los siguientes tipos de datos en el archivo de formato: SQLCHAR o SQLVARYCHAR (los datos se envían en la página de códigos del cliente o en la página de códigos implícita en la intercalación), SQLNCHAR o SQLNVARCHAR (los datos se envían como Unicode), o SQLBINARY o SQLVARYBIN (los datos se envían sin ninguna conversión).

Ejemplos

Los ejemplos de esta sección muestran cómo usar los archivos de formato para importar datos de forma masiva mediante el comando bcp y las instrucciones BULK INSERT e INSERT ... SELECT * FROM OPENROWSET(BULK...). Para poder ejecutar los ejemplos de importación masiva, debe crear una tabla, un archivo de datos y un archivo de formato de ejemplo.

Tabla de ejemplo

Los ejemplos requieren la creación de una tabla denominada myTestFormatFiles en la base de datos de ejemplo AdventureWorks bajo el esquema dbo. Para crear esta tabla, en el Editor de consultas de SQL Server Management Studio, ejecute:

USE AdventureWorks;
GO
CREATE TABLE myTestFormatFiles (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO

Archivo de datos de ejemplo

Los ejemplos utilizan un archivo de datos de ejemplo, myTestFormatFiles-c.Dat, que contiene los siguientes registros. Para crear el archivo de datos, en el símbolo de sistema de Microsoft Windows, escriba:

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Archivos de formato de ejemplo

Algunos ejemplos de esta sección utilizan un archivo de formato XML, myTestFormatFiles-f-x-c.Xml, mientras que otros ejemplos utilizan un archivo sin formato XML. Ambos archivos de formato utilizan formatos de datos de caracteres y un terminador de campo que no es el predeterminado (,).

Archivo de formato no XML de ejemplo

En el siguiente ejemplo se utiliza bcp para generar un archivo de formato XML a partir de la tabla myTestFormatFiles. El archivo myTestFormatFiles.Fmt incluye la siguiente información:

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

Para utilizar bcp con la opción format para crear este archivo de formato, en el símbolo de sistema de Windows, escriba:

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T

Para obtener más información acerca de cómo crear un archivo de formato, vea Crear un archivo de formato.

Archivo de formato XML de ejemplo

En el siguiente ejemplo se utiliza bcp para generar un archivo de formato XML a partir de la tabla myTestFormatFiles. El archivo myTestFormatFiles.Xml incluye 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="7"/>
  <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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Para utilizar bcp con la opción format para crear este archivo de formato, en el símbolo de sistema de Windows, escriba:

bcp AdventureWorks..MyTestFormatFiles format nul -c -t, -x -f myTestFormatFiles.Xml -T

Usar bcp

El ejemplo siguiente utiliza bcp para importar datos de forma masiva desde el archivo de datos myTestFormatFiles-c.Dat a la tabla HumanResources.myTestFormatFiles de la base de datos de ejemplo AdventureWorks. Este ejemplo utiliza un archivo de formato XML, MyTestFormatFiles.Xml. El ejemplo elimina todas las filas existentes en la tabla antes de importar el archivo de datos.

En el símbolo del sistema de Windows, escriba:

bcp AdventureWorks..myTestFormatFiles in C:\myTestFormatFiles-c.Dat -f C:\myTestFormatFiles.Xml -T

[!NOTA] Para obtener más información acerca de este comando, vea bcp (utilidad).

Usar BULK INSERT

El ejemplo siguiente utiliza BULK INSERT para importar datos de forma masiva desde el archivo de datos myTestFormatFiles-c.Dat a la tabla HumanResources.myTestFormatFiles de la base de datos de ejemplo AdventureWorks. Este ejemplo utiliza un archivo sin formato XML, MyTestFormatFiles.Fmt. El ejemplo elimina todas las filas existentes en la tabla antes de importar el archivo de datos.

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

USE AdventureWorks;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

[!NOTA] Para obtener más información acerca de esta instrucción, vea BULK INSERT (Transact-SQL).

Usar el proveedor de conjunto de filas bulk OPENROWSET

El ejemplo siguiente utiliza INSERT ... SELECT * FROM OPENROWSET(BULK...) para importar datos de forma masiva desde el archivo de datos myTestFormatFiles-c.Dat a la tabla HumanResources.myTestFormatFiles de la base de datos de ejemplo AdventureWorks. Este ejemplo utiliza un archivo de formato XML, MyTestFormatFiles.Xml. El ejemplo elimina todas las filas existentes en la tabla antes de importar el archivo de datos.

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

USE AdventureWorks;
DELETE myTestFormatFiles;
GO
INSERT INTO myTestFormatFiles
    SELECT *
      FROM  OPENROWSET(BULK  'C:\myTestFormatFiles-c.Dat',
      FORMATFILE='C:\myTestFormatFiles.Xml'     
      ) as t1 ;
GO
SELECT * FROM myTestFormatFiles;
GO

Cuando haya terminado de utilizar la tabla de ejemplo, puede eliminarse con la siguiente instrucción:

DROP TABLE myTestFormatFiles

[!NOTA] Para obtener más información sobre la cláusula OPENROWSET BULK, vea OPENROWSET (Transact-SQL).

Vea también