sqlserver大批量导入数据，从其他库导入，解决字段不一致问题

由于现有项目想从别的系统导入一批新数据，数据量庞大且字段不一致，可以使用BULK来解决

打开配置

/*
配置工作开始  打开配置
*/
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
-- 关闭所有触发器
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

目标库 A 数据源库 B 

现在要从B库中将trk_invd表的数据导入到A库中的trk_invd表

A库中的结构
 

CREATE TABLE [dbo].[trk_invd] (
  [ref] int  IDENTITY(1,1) NOT NULL,
  [invn] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [gat] int  NOT NULL,
  [amt] numeric(18,2)  NULL,

  [gat2] int  NOT NULL,
  [amt2] numeric(18,2)  NULL,
  [crtuser] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [crtdate] datetime  NULL,
  [upduser] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [upddate] datetime  NULL
)

B库中的结构

CREATE TABLE [dbo].[trk_invd] (
  [ref] int  IDENTITY(1,1) NOT NULL,
  [invn] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [gat] int  NOT NULL,
  [amt] numeric(18,2)  NULL,
  [crtuser] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [crtdate] datetime  NULL,
  [upduser] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [upddate] datetime  NULL
)

建立中间视图 trk_invd_v 查询出自己想要导出的数据

CREATE VIEW trk_invd_v AS 
select * ,null as gat2,null as amt2
FROM
    ek_cms_bak.dbo.trk_invd 
WHERE
    invn IN ( SELECT invn FROM trk_inv WHERE depo IN ( 'EKJ', 'MY03' ) AND crtdate >= '2021-07-01' );

A库中的表比B表多了两个字段

导出A表xml结构文件

EXEC master..xp_cmdshell 'BCP EK_CMS_NEK1.dbo.trk_invd format nul -c -x -f D:/trk_invd_fmt.xml -t,-r, -T '
GO

-t后面跟的是数据的分隔符 这是个坑 默认是/t /n 如果数据中含有换行符导入的时候就会失败 所以这里随便写了一个参数,-r, 导入数据的时候就不会失败

根据A库的结构导出中间视图的数据

EXEC master..xp_cmdshell 'BCP EK_CMS_NEK1.dbo.trk_invd_v out D:/trk_invd.data -f D:/trk_invd_fmt.xml -T'
GO

开始导入数据

第二个坑点 如果想要id自增的话就要这么导入
 

BULK INSERT EK_CMS_NEK1.dbo.trk_invd
FROM N'D:/trk_invd.data'
WITH
(
FORMATFILE = N'D:/trk_invd_fmt.xml'
)
GO

我这里是不想要id自增  需要改变一些写法

SET IDENTITY_INSERT EK_CMS_NEK1.dbo.trk_invd ON
BULK INSERT EK_CMS_NEK1.dbo.trk_invd
FROM N'D:/trk_invd.data'
WITH
(
FORMATFILE = N'D:/trk_invd_fmt.xml',
KEEPIDENTITY
)
set IDENTITY_INSERT EK_CMS_NEK1.dbo.trk_invd OFF
GO

最后结束的时候把配置关闭

/*
关闭配置
*/
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO
-- 打开触发器
EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO

这样就完成了

详细的BULK参数用法可以自行学习一下