Leer en inglés

Compartir a través de

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

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Un archivo de datos puede contener campos organizados en un orden diferente al de las columnas correspondientes en la tabla. En este artículo se presentan los archivos de formato XML y XML que se modifican para dar cabida a un archivo de datos cuyos campos se organizan en un orden diferente de las columnas de la tabla. El archivo de formato modificado asigna los campos de datos a las columnas correspondientes de la tabla. Consulte Creación de un archivo de formato con bcp (SQL Server) para obtener información adicional.

Se puede usar un archivo de formato XML o no XML para importar de forma masiva un archivo de datos en la tabla, mediante un comando de utilidad bcp, una instrucción BULK INSERT o INSERT ... INSTRUCCIÓN SELECT * FROM OPENROWSET . Para obtener más información, vea Usar un archivo de formato para importar datos de forma masiva (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.

Condiciones de prueba de ejemplo

Los ejemplos de archivos de formato modificados de este artículo se basan en la tabla y el archivo de datos definido más adelante en este artículo.

Tabla de ejemplo

El siguiente script de Transact-SQL crea una base de datos de prueba y una tabla denominada myRemap.

SQL 
CREATE DATABASE TestDatabase;
GO

USE TestDatabase;

CREATE TABLE myRemap
(
    PersonID SMALLINT,
    FirstName VARCHAR (25),
    LastName VARCHAR (30),
    Gender CHAR (1)
);

Archivo de datos de ejemplo

Los datos siguientes presentan FirstName y LastName en el orden inverso tal como se muestra en la tabla myRemap. Con un editor de texto como el Bloc de notas, cree un archivo D:\BCP\myRemap.bcp vacío e inserte los datos siguientes:

csv 
1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Crear los archivos de formato

Para importar datos de forma masiva desde myRemap.bcp en la myRemap tabla, el archivo de formato debe realizar las siguientes acciones:

  • Asignar el primer campo de datos a la primera columna, PersonID.
  • Asignar el segundo campo de datos a la tercera columna, LastName.
  • Asignar el tercer campo de datos a la segunda columna, FirstName.
  • Asignar el cuarto campo de datos a la cuarta columna, Gender.

Se trata del método más sencillo para crear el archivo de formato mediante la utilidad bcp. En primer lugar, cree un archivo de formato base a partir de la tabla existente. En segundo lugar, modifique el archivo de formato base para reflejar el archivo de datos real.

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 usa la utilidad bcp para generar un archivo de formato no XML, , myRemap.fmtbasado en el esquema de myRemap. Además, el calificador c se utiliza para especificar los datos de caracteres, t, se utiliza para especificar una coma como terminador de campo y T se utiliza para especificar una conexión de confianza que usa seguridad integrada. En el símbolo del sistema, escriba el siguiente comando:

Símbolo del sistema de Windows 
bcp TestDatabase.dbo.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Modificación del archivo de formato no XML

Para obtener información sobre la terminología, vea Estructura de archivos de formato no XML. Abra D:\BCP\myRemap.fmt en el Bloc de notas y realice las modificaciones siguientes:

  1. Reorganiza el orden de las filas de archivo de formato para que las filas estén en el mismo orden que los datos de myRemap.bcp.
  2. Asegúrese de que los valores del orden del campo del archivo de host son secuenciales.
  3. Asegúrese de que haya un retorno de carro después de la última fila de archivo de formato.

Compare los cambios:

Antes

Output 
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       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

Después

Output 
13.0
4
1       SQLCHAR    0       7       ","      1     PersonID               ""
2       SQLCHAR    0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR    0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR    0       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

El archivo de formato modificado ahora refleja:

  • El primer campo de datos de myRemap.bcp está asignado a la primera columna, myRemap.. PersonID
  • El segundo campo de datos de myRemap.bcp está asignado a la tercera columna, myRemap.. LastName
  • El tercer campo de datos de myRemap.bcp está asignado a la segunda columna, myRemap.. FirstName
  • El cuarto campo de datos de myRemap.bcp está asignado a la cuarta columna, myRemap.. Gender

Creación de un archivo de formato XML

Consulta Archivos de formato XML (SQL Server) para información detallada. El siguiente comando usa la utilidad bcp para crear un archivo de formato xml, , myRemap.xmlen función del esquema de myRemap. Además, el calificador c se utiliza para especificar los datos de caracteres, t, se utiliza para especificar una coma como terminador de campo y T se utiliza para especificar una conexión de confianza que usa seguridad integrada. El calificador x se debe usar para generar un archivo de formato basado en XML. En el símbolo del sistema, escriba el siguiente comando:

Símbolo del sistema de Windows 
bcp TestDatabase.dbo.myRemap format nul -c -x -f D:\BCP\myRemap.xml -t, -T

Modificación del archivo de formato XML

Consulta Sintaxis de esquema para archivos de formato XML para terminología. Abra D:\BCP\myRemap.xml en el Bloc de notas y realice las modificaciones siguientes:

  1. El orden en el que se declaran los <FIELD> elementos en el archivo de formato es el orden en el que aparecen esos campos en el archivo de datos. Por lo tanto, invierte el orden de los <FIELD> elementos con atributos id. 2 y 3.

  2. Asegúrese de que los valores de atributo de id. <FIELD> son secuenciales.

  3. El orden de los <COLUMN> elementos del <elemento ROW> define el orden en el que la operación masiva los envía al destino. El archivo de formato XML asigna a cada elemento <COLUMN> un nombre local que no tiene ninguna relación con la columna de la tabla de destino de una operación de importación en bloque. El orden de los <COLUMN> elementos es independiente del orden de los elementos en <FIELD> una <definición RECORD> . Cada elemento <COLUMN> corresponde a un elemento <FIELD> (cuyo id. se especifica en el atributo SOURCE del elemento <COLUMN>). Por lo tanto, los valores de <COLUMN> SOURCE son los únicos atributos que requieren revisión. Invierta el orden de los atributos 2 y 3 de <COLUMN> SOURCE.

Compare los cambios:

Antes

XML 
<?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="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

Después

XML 
<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

El archivo de formato modificado ahora refleja:

  • FIELD 1, que corresponde a COLUMN 1, se asigna a la primera columna de tabla, myRemap.. PersonID
  • FIELD 2, que corresponde a COLUMN 2, se reasigna a la tercera columna de tabla, myRemap.. LastName
  • FIELD 3, que corresponde a COLUMN 3, se reasigna a la segunda columna de tabla, myRemap.. FirstName
  • FIELD 4, que corresponde a COLUMN 4, se asigna a la cuarta columna de tabla, myRemap.. Gender

Importación de datos con un archivo de formato para asignar columnas de tabla a campos de un archivo de datos

En los ejemplos siguientes se usan la base de datos, el archivo de datos y los archivos de formato creados anteriormente.

Uso de bcp y un archivo de formato no XML

En un símbolo del sistema, escriba el siguiente comando.

Símbolo del sistema de Windows 
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Uso de bcp y un archivo de formato XML

En un símbolo del sistema, escriba el siguiente comando.

Símbolo del sistema de Windows 
bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

Uso de BULK INSERT y un archivo de formato no XML

Ejecute el siguiente script de Transact-SQL.

SQL 
USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');
GO

-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;

Uso de BULK INSERT y un archivo de formato XML

Ejecute el siguiente script de Transact-SQL.

SQL 
USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

BULK INSERT dbo.myRemap FROM 'D:\BCP\myRemap.bcp'
    WITH (FORMATFILE = 'D:\BCP\myRemap.xml');
GO

-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;

Usar OPENROWSET y archivo de formato no XML

Ejecute el siguiente script de Transact-SQL.

SQL 
USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.fmt'
) AS t1;
GO

-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;

Uso del archivo de formato OPENROWSET y XML

Ejecute el siguiente script de Transact-SQL.

SQL 
USE TestDatabase;
GO

TRUNCATE TABLE myRemap;

INSERT INTO dbo.myRemap
SELECT *
FROM OPENROWSET (
    BULK 'D:\BCP\myRemap.bcp',
    FORMATFILE = 'D:\BCP\myRemap.xml'
) AS t1;
GO

-- review results
SELECT *
FROM TestDatabase.dbo.myRemap;

Contenido relacionado