As a general practice, there is no way to generate a formatted XML file when you export some of the fields, so there is no way to format the imported data when importing.
I think of two points, 1. Manually modify the formatted XML file, 2. Create an intermediate object that generates a formatted XML file.
When looking for methods in MSDN, it suddenly occurred to me that you could use a view to do the middle object and then take a test. Here is the Test record:
Copy Code code 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),
Memo nvarchar (500),
Memo2 ntext,
PRIMARY KEY (ID)
)
Go
--The table created above is the source datasheet, and the following creates the table to which you want to import data, with only three fields from 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,name from the table t_test into the t_test2.
-Next, you generate a formatted XML file that imports the destination table, but MSDN says you can only generate a formatted XML file for an object.
--had to build an intermediate object to achieve the goal, where I created a view.
--The view contains only fields that need 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_cmdshell ', 1;
EXEC sp_configure ' show advanced options ', 0;
Reconfigure;
Go
EXEC Master.. xp_cmdshell ' BCP db_test.dbo.v_test format nul-f c:/v_test_fmt.xml-x-c-t-S Mypc\mydb '
Go
EXEC Master.. xp_cmdshell ' 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 files and data files are available, it becomes.
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_cmdshell ', 0;
EXEC sp_configure ' show advanced options ', 0;
Reconfigure;
Go
Drop Database Db_test
Go
The environment is sql2005.
