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 tema se describe la modificación de archivos de formato XML y no XML para adaptarse a un archivo de datos con más campos asignando las columnas de la tabla a los campos de datos correspondientes y omitiendo los campos adicionales. Revise Crear un archivo de formato (SQL Server) para información adicional.
Nota
Puede usarse un archivo de formato XML o no XML para importar en bloque un archivo de datos en la tabla mediante un comando de la utilidad bcp, una instrucción BULK INSERT o una instrucción INSERT… SELECT * FROM OPENROWSET(BULK...). Para más información, vea Uso de un archivo de formato para la importación masiva de datos (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 tema se basan en la tabla y archivo de datos definidos a continuación.
Tabla de ejemplo
El script siguiente crea una base de datos de prueba y una tabla llamada myTestSkipField. Ejecutar 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
Creación de 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.
Crear un archivo de formato no XML
Revise 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
Modificar el archivo de formato no XML
Revise la estructura de los archivos de formato no XML para obtener terminología. Abra D:\BCP\myTestSkipField.fmt en el Bloc de notas y realice las modificaciones siguientes:
- Copie toda la fila del archivo de formato para
FirstNamey péguela directamente después deFirstNameen la línea siguiente. - Aumente el valor del pedido del campo de archivo de host en 1 para la fila nueva y todas las filas subsiguientes.
- Aumente el valor del número de columnas para reflejar el número real de campos en el archivo de datos.
- Modifique el orden de la columna del servidor de
2a0en 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.bcpestá asignado a la primera columna,myTestSkipField.. PersonID - El segundo campo de datos de
myTestSkipField.bcpno está asignado a ninguna columna. - El tercer campo de datos de
myTestSkipField.bcpestá asignado a la segunda columna,myTestSkipField.. FirstName - El cuarto campo de datos de
myTestSkipField.bcpestá asignado a la tercera columna.myTestSkipField.. LastName
Creación de un archivo de formato XML
Revise 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. 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. 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
Modificar el archivo de formato XML
Revise la sintaxis del esquema para los archivos de formato XML para obtener terminología. Abra D:\BCP\myTestSkipField.xml en el Bloc de notas y realice las modificaciones siguientes:
- Copie todo el segundo campo y péguelo directamente después del segundo campo en la línea siguiente.
- Aumente el valor "FIELD ID" en 1 para el nuevo campo FIELD y cada campo FIELD subsiguiente.
- Aumente el valor de "COLUMN SOURCE" en 1 para
FirstNameyLastNamepara 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 y, por lo tanto, 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
Importar datos con un archivo de formato para omitir un campo de datos
Los ejemplos siguientes usan la base de datos, el archivo de datos y los archivos de formato que se han creado anteriormente.
Uso de bcp y 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
Uso de bcp y archivo de formato XML
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
Uso de BULK INSERT y archivo de formato no XML
Ejecutar 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;
Uso del archivo de formatoBULK INSERT y XML
Ejecutar 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...) y archivo de formato no XML
Ejecutar 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...) y archivo de formato XML
Ejecutar 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;
Pasos siguientes
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de