SQL Server Bulk Insert only requires partial Fields

According to the general practice, some fields cannot be exported to generate the formatted XML file, so there is no way to format the imported data during the import.

I think of two points: 1. manually modify and format the XML file; 2. Create an intermediate object that can generate and format the XML file.

When searching for methods in MSDN, I suddenly thought that I could use the view for intermediate objects, so I took a test. The following are test records:

Copy codeThe Code is as follows: USE master
GO
Create database [db_test]
GO
USE db_test
GO
Create table dbo. T_test (
ID [int] IDENTITY (1, 1) not null,
Code varchar (10 ),
Name varchar (100 ),
Apsaradb for Memo nvarchar (500 ),
Memo2 ntext,
Primary key (ID)
)
GO
-- The table created above is the source data table, and the table to be imported is created below, only three fields of the source table
Select Code, Name, Memo into dbo. T_test2 from dbo. T_test Where 1 = 2

-- The requirement is to import the Code and Name in the T_test table to T_test2.
-- The next step is to generate a formatted XML file for the import target table. However, in MSDN, only a formatted XML file for an object can be generated.
-- We have to create an intermediate object for the purpose. Here we create a view.
-- The view only contains the fields to be imported
Create View v_test
AS
Select Code, Name From dbo. T_test2
-- Then the BCP operation
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp _ Your shell', 1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
EXEC master .. xp_mongoshell 'bcp db_test.dbo.v_test format nul-f C:/v_test_fmt.xml-x-c-T-S MyPC \ mydb'
GO
EXEC master .. xp_mongoshell 'bcp "select Code, Name from db_test.dbo.T_test" queryout C:/t_test.data-f C:/v_test_fmt.xml-T-S MyPC \ mydb'
GO

-- Format the file and data file.
Bulk insert db_mgr.dbo.v_t1
From n 'C:/t_test.data'
WITH
(
FORMATFILE = n'c:/v_test_fmt.xml'
)
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp _ Your shell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
Drop Database db_test
GO

The environment is sql2005.