Compartir a través de

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

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Un archivo de datos puede contener más campos que el número de columnas de la tabla. En este artículo se describe la modificación de archivos de formato no XML y XML para dar cabida a un archivo de datos con más campos asignando las columnas de tabla a los campos de datos correspondientes e ignorando los campos adicionales.

Para obtener más información, vea Crear un archivo de formato con bcp (SQL Server).

Nota:

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, BULK INSERT (Transact-SQL) instrucción o INSERT ... INSTRUCCIÓN SELECT * FROM OPENROWSET BULK (Transact-SQL). 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 myTestSkipField de ejemplo y el archivo D:\BCP\myTestSkipField.bcpde datos . Cambie la ubicación del archivo local en el ejemplo de código a una ubicación de archivo en el equipo.

Tabla de ejemplo

El script crea una base de datos de prueba y una tabla denominada myTestSkipField. Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

Archivo de datos de ejemplo

Cree un archivo vacío D:\BCP\myTestSkipField.bcp e inserte los datos siguientes:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Crear los archivos de formato

Para realizar una importación masiva de datos de myTestSkipField.bcp en la tabla myTestSkipField , el archivo de formato debe llevar a cabo lo siguiente:

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

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

bcp TestDatabase.dbo.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.fmt -t, -T

Modificación del archivo de formato no XML

Consulta Estructura de los archivos de formato no XML para terminología. Abra D:\BCP\myTestSkipField.fmt en el Bloc de notas y realice las modificaciones siguientes:

  1. Copie toda la fila del archivo de formato para FirstName y péguela directamente después de FirstName en la línea siguiente.
  2. Aumente el valor del pedido del campo de archivo de host en 1 para la fila nueva y todas las filas subsiguientes.
  3. Aumente el valor del número de columnas para reflejar el número real de campos en el archivo de datos.
  4. Modifique el orden de la columna del servidor de 2 a 0 en la segunda fila del archivo de formato.

Compare los cambios realizados:

Antes

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

Después

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

El archivo de formato modificado ahora refleja:

  • 4 campos de datos
  • El primer campo de datos de myTestSkipField.bcp está asignado a la primera columna, myTestSkipField.. PersonID
  • El segundo campo de datos de myTestSkipField.bcp no está asignado a ninguna columna.
  • El tercer campo de datos de myTestSkipField.bcp está asignado a la segunda columna, myTestSkipField.. FirstName
  • El cuarto campo de datos de myTestSkipField.bcp está asignado a la tercera columna. myTestSkipField.. LastName

Creación de un archivo de formato XML

Revise los archivos de formato XML (SQL Server) para obtener información detallada. El siguiente comando hará uso de la utilidad bcp para crear un archivo de formato xml, myTestSkipField.xml, basado en el esquema de myTestSkipField.

  • El calificador c se usa para especificar datos de caracteres
  • t, se usa para especificar una coma como terminador de campo
  • T se usa para especificar una conexión de confianza mediante la 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:

bcp TestDatabase.dbo.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.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\myTestSkipField.xml en el Bloc de notas y realice las modificaciones siguientes:

  1. Copie todo el segundo campo y péguelo directamente después del segundo campo en la línea siguiente.
  2. Aumente el FIELD ID valor en 1 para el nuevo FIELD y para cada posterior FIELD.
  3. Aumente el COLUMN SOURCE valor en 1 para FirstNamey LastName para reflejar la asignación revisada.

Compare los cambios realizados:

Antes

<?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="\r\n" MAX_LENGTH="30" 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"/>
</ROW>
</BCPFORMAT>

Después

<?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="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

El archivo de formato modificado ahora refleja:

  • 4 campos de datos
  • FIELD 1 que corresponde a COLUMN 1 se asigna a la primera columna de tabla, myTestSkipField.. PersonID
  • FIELD 2 no corresponde a ninguna COLUMN columna de tabla y, por tanto, no se asigna a ninguna columna de tabla.
  • FIELD 3 que corresponde a COLUMN 3 se asigna a la segunda columna de tabla, myTestSkipField.. FirstName
  • FIELD 4 que corresponde a COLUMN 4 se asigna a la tercera columna de tabla, myTestSkipField.. LastName

Importación de datos con un archivo de formato para omitir un campo de datos

Los ejemplos usan la base de datos de ejemplo, el archivo de datos y los archivos de formato creados en este artículo.

Uso de bcp y un archivo de formato no XML

En el símbolo del sistema, escriba el siguiente comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.fmt -T

Usar archivos de formato bcp y XML (SQL Server)

En el símbolo del sistema, escriba el siguiente comando:

bcp TestDatabase.dbo.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.xml -T

Usar BULK INSERT (Transact-SQL) y archivo de formato no XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Usar BULK INSERT (Transact-SQL) y archivos de formato XML (SQL Server)

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Usar OPENROWSET BULK (Transact-SQL) y archivo de formato no XML

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

Usar OPENROWSET BULK (Transact-SQL) y archivos de formato XML (SQL Server)

Ejecute el siguiente Transact-SQL en Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Contenido relacionado

  • Last updated on