Compartir a través de

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

En este tema se describen los archivos de formato. Puede usar un archivo de formato para omitir la importación de una columna de tabla cuando el campo no existe en el archivo de datos. Un archivo de datos puede contener menos campos que el número de columnas de la tabla solo si las columnas omitidas admiten valores NULL o tienen un valor predeterminado.

Tabla de ejemplo y archivo de datos

Los ejemplos siguientes requieren una tabla denominada myTestSkipCol en la base de datos de ejemplo AdventureWorks2012 en el esquema dbo . Cree esta tabla de la siguiente manera:

USE AdventureWorks2012;
GO
CREATE TABLE myTestSkipCol 
   (
   Col1 smallint,
   Col2 nvarchar(50) NULL,
   Col3 nvarchar(50) not NULL
   );
GO

En los ejemplos siguientes se usa un archivo de datos de ejemplo, , myTestSkipCol2.datque contiene solo dos campos, aunque la tabla correspondiente contiene tres columnas:

1,DataForColumn3
1,DataForColumn3
1,DataForColumn3

Para importar datos de forma masiva desde myTestSkipCol2.dat en la myTestSkipCol tabla, el archivo de formato debe asignar el primer campo de datos a Col1, el segundo campo a Col3, omitiendo Col2.

Usar un archivo de formato no XML

Puede modificar un archivo de formato no XML para omitir una columna de tabla. Normalmente, esto implica usar la utilidad bcp para crear un archivo de formato no XML predeterminado y modificar el archivo predeterminado en un editor de texto. El archivo de formato modificado debe asignar cada campo existente a su columna de tabla correspondiente e indicar qué columnas o columnas de tabla se van a omitir. Existen dos alternativas para modificar un archivo de datos no XML predeterminado. Cualquier alternativa indica que el campo de datos no existe en el archivo de datos y que no se insertará ningún dato en la columna de tabla correspondiente.

Crear un archivo de formato no XML predeterminado

En este tema se usa el archivo de formato no XML predeterminado que se creó para la myTestSkipCol tabla de ejemplo mediante el siguiente comando bcp :

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T

El comando anterior crea un archivo de formato no XML, myTestSkipCol_Default.fmt. Este archivo de formato se denomina archivo de formato predeterminado porque es el formulario generado por bcp. Normalmente, un archivo de formato predeterminado describe una correspondencia uno a uno entre los campos de archivo de datos y las columnas de tabla.

Importante

Es posible que tenga que especificar el nombre de la instancia del servidor a la que se va a conectar. Además, es posible que tenga que especificar el nombre de usuario y la contraseña. Para obtener más información, consulte bcp Utility.

En la ilustración siguiente se muestran los valores de este ejemplo de archivos de formato predeterminado. La ilustración también muestra el nombre de cada campo de archivo de formato.

archivo de formato no XML predeterminado para myTestSkipCol

Nota:

Para obtener más información sobre los campos de archivo de formato, vea Archivos de formato no XML (SQL Server).

Métodos para modificar un archivo de formato no XML

Para omitir una columna de tabla, edite el archivo de formato no XML predeterminado y modifique el archivo mediante uno de los métodos alternativos siguientes:

  • El método preferido implica tres pasos básicos. En primer lugar, elimine cualquier fila de archivo de formato que describa un campo que falta en el archivo de datos. A continuación, reduzca el valor "Orden de campo de archivo de host" de cada fila de archivo de formato que sigue a una fila eliminada. El objetivo es valores secuenciales del "orden de campo del archivo de host", desde 1 hasta n, que reflejen la posición real de cada campo de datos en el archivo de datos. Por último, reduzca el valor del campo "Número de columnas" para reflejar el número real de campos del archivo de datos.

    El ejemplo siguiente se basa en el archivo de formato predeterminado de la myTestSkipCol tabla, que se crea en "Crear un archivo de formato no XML predeterminado", anteriormente en este tema. Este archivo de formato modificado asigna el primer campo de datos a Col1, omite Col2y asigna el segundo campo de datos a Col3. Se ha eliminado la fila de Col2 . Otras modificaciones se indican en negrita:

    9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

  • Como alternativa, para omitir una columna de tabla, puede modificar la definición de la fila de archivo de formato que corresponde a la columna de tabla. En esta fila de archivo de formato, los valores "longitud del prefijo", "longitud de datos del archivo host" y "orden de columna del servidor" deben establecerse en 0. Además, los campos "terminador" y "intercalación de columnas" deben establecerse en "" (NULL).

    El valor de "nombre de columna de servidor" requiere una cadena no en blanco, aunque el nombre de columna real no es necesario. Los campos de formato restantes requieren sus valores predeterminados.

    El ejemplo siguiente también se deriva del archivo de formato predeterminado para la myTestSkipCol tabla. Los valores que deben ser 0 o NULL se indican en negrita.

    9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       00""0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Ejemplos

Los ejemplos siguientes también se basan en la myTestSkipCol tabla de ejemplo y en el myTestSkipCol2.dat archivo de datos de ejemplo que se crean en "Tabla de ejemplo y archivo de datos", anteriormente en este tema.

Usar BULK INSERT

En este ejemplo se usa cualquiera de los archivos de formato no XML modificados creados en "Métodos para modificar un archivo de formato no XML", anteriormente en este tema. En este ejemplo, el archivo de formato modificado se denomina C:\myTestSkipCol2.fmt. Para usar BULK INSERT para importar de forma masiva el myTestSkipCol2.dat archivo de datos, en el Editor de consultas de SQL Server Management Studio, ejecute el código siguiente:

USE AdventureWorks2012;
GO
BULK INSERT myTestSkipCol 
   FROM 'C:\myTestSkipCol2.dat' 
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');
GO
SELECT * FROM myTestSkipCol;
GO

Usar un archivo de formato XML

Con un archivo de formato XML, no se puede omitir una columna cuando se importa directamente en una tabla mediante un comando bcp o una instrucción BULK INSERT. Sin embargo, puede importar en todas las columnas de una tabla, excepto en la última. Si tiene que omitir todas las columnas excepto la última, debe crear una vista de la tabla de destino que incluya únicamente las columnas presentes en el archivo de datos. A continuación, puede importar datos de forma masiva desde ese archivo a la vista.

Para usar un archivo de formato XML para omitir una columna de tabla mediante OPENROWSET(BULK...), debe proporcionar una lista explícita de columnas en la lista de selección y también en la tabla de destino, como se indica a continuación:

INSERTAR...<> column_list SELECT <column_list> FROM OPENROWSET(BULK...)

Crear un archivo de formato XML predeterminado

Los ejemplos de archivos de formato modificados se basan en la myTestSkipCol tabla de ejemplo y el archivo de datos que se crean en "Tabla de ejemplo y archivo de datos", anteriormente en este tema. El siguiente comando bcp crea un archivo de formato XML predeterminado para la myTestSkipCol tabla:

bcp AdventureWorks2012..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T

El archivo de formato predeterminado no XML resultante describe una correspondencia uno a uno entre los campos de archivo de datos y las columnas de tabla, como se indica a continuació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="\t" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" 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"/>
 </ROW>
</BCPFORMAT>

Nota:

Para obtener información sobre la estructura de los archivos de formato XML, vea Archivos de formato XML (SQL Server).

Ejemplos

Los ejemplos de esta sección usan la myTestSkipCol tabla de ejemplo y el myTestSkipCol2.dat archivo de datos de ejemplo que se crean en "Tabla de ejemplo y archivo de datos", anteriormente en este tema. Para importar los datos de myTestSkipCol2.dat en la tabla myTestSkipCol, los ejemplos utilizan el siguiente archivo de formato XML modificado, myTestSkipCol2-x.xml. Esto se basa en el archivo de formato que se crea en "Crear un archivo de formato XML predeterminado", anteriormente en este tema.

<?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="\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="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Uso de OPENROWSET(BULK...)

El siguiente ejemplo utiliza el OPENROWSET proveedor de conjunto de filas en masa y el archivo de myTestSkipCol2.xml formato. En el ejemplo se importa masivamente el myTestSkipCol2.dat archivo de datos en la myTestSkipCol tabla. La instrucción contiene una lista explícita de columnas en la lista de selección y también en la tabla de destino, como se requiere.

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

USE AdventureWorks2012;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',
      FORMATFILE='C:\myTestSkipCol2.Xml'  
       ) as t1 ;
GO

Uso de BULK IMPORT en una vista

En el ejemplo siguiente se crea la myTestSkipCol tabla en v_myTestSkipCol. Esta vista omite la segunda columna de tabla, Col2. A continuación, el ejemplo usa BULK INSERT para importar el myTestSkipCol2.dat archivo de datos en esta vista.

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

CREATE VIEW v_myTestSkipCol AS
    SELECT Col1,Col3
    FROM myTestSkipCol;
GO

USE AdventureWorks2012;
GO
BULK INSERT v_myTestSkipCol
FROM 'C:\myTestSkipCol2.dat'
WITH (FORMATFILE='C:\myTestSkipCol2.xml');
GO

Véase también

bcp UtilityBULK INSERT (Transact-SQL)OPENROWSET (Transact-SQL)Usar un archivo de formato para omitir un campo de datos (SQL Server)Usar un archivo de formato para asignar columnas de tabla a Data-File campos (SQL Server)Usar un archivo de formato para importar datos de forma masiva (SQL Server)

  • Last updated on