I've tried using SqlBulkCopy to migration data between different database and schema. This is sql command get data from source database:
SELECT TOP 2000000 SUBSTRING(HANM002001,1,8) AS HANM002001,
(HANM002004 +' '+HANM002005) AS HANM002004,
HANM002007,
'' AS col4,
HANM002008,
(HANM002009+' '+HANM002010) AS HANM002009,
SUBSTRING(HANM002013,1,13) AS HANM002012,
SUBSTRING(HANM002013,1,13) AS HANM002013,
'' AS col9,
0 AS col10,
0 AS col11,
HANM002027,
HANM002030,
HANM002033,
'' AS col15,
'' AS col16,
'' AS col17,
'' AS col18,
'' AS col19,
'' AS col20,
'' AS col21,
'' AS col22,
'' AS col23,
0 AS col24,
0 AS col25,
'' AS col26,
HANM002INS
FROM HAN07M002SHIIRE
And here is destination table schema:
CREATE TABLE [dbo].[mst仕入先](
[仕入先コード] [varchar](8) NOT NULL,
[仕入先名] [nvarchar](40) NULL,
[仕入先名カナ] [nvarchar](30) NULL,
[仕入先略称] [nvarchar](14) NULL,
[郵便番号] [varchar](10) NULL,
[住所] [nvarchar](100) NULL,
[電話番号] [varchar](13) NULL,
[FAX番号] [varchar](13) NULL,
[担当者コード] [varchar](6) NULL,
[税計算区分] [smallint] NULL,
[端数区分] [smallint] NULL,
[締日] [smallint] NULL,
[入金日] [smallint] NULL,
[入金サイクル] [smallint] NULL,
[銀行コード] [varchar](4) NULL,
[支店コード] [varchar](3) NULL,
[口座番号] [varchar](7) NULL,
[受取人名] [nvarchar](30) NULL,
[業者コード] [varchar](6) NULL,
[買掛仕入コード] [varchar](6) NULL,
[買掛経費コード] [varchar](6) NULL,
[現金コード] [varchar](6) NULL,
[表示区分] [smallint] NULL,
[削除フラグ] [smallint] NULL,
[更新者] [varchar](2) NULL,
[更新日] [datetime] NULL,
PRIMARY KEY CLUSTERED ( [仕入先コード] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Here is code mapping:
Using sqlCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection.ConnectionString)
sqlCopy.ColumnMappings.Add("HANM002001", "仕入先コード")
sqlCopy.ColumnMappings.Add("HANM002004", "仕入先名")
sqlCopy.ColumnMappings.Add("HANM002007", "仕入先名カナ")
sqlCopy.ColumnMappings.Add("col4", "仕入先略称")
sqlCopy.ColumnMappings.Add("HANM002008", "郵便番号")
sqlCopy.ColumnMappings.Add("HANM002009", "住所")
sqlCopy.ColumnMappings.Add("HANM002012", "電話番号")
sqlCopy.ColumnMappings.Add("HANM002013", "FAX番号")
sqlCopy.ColumnMappings.Add("col9", "担当者コード")
sqlCopy.ColumnMappings.Add("col10", "税計算区分")
sqlCopy.ColumnMappings.Add("col11", "端数区分")
sqlCopy.ColumnMappings.Add("HANM002027", "締日")
sqlCopy.ColumnMappings.Add("HANM002030", "入金日")
sqlCopy.ColumnMappings.Add("HANM002033", "入金サイクル")
sqlCopy.ColumnMappings.Add("col15", "銀行コード")
sqlCopy.ColumnMappings.Add("col16", "支店コード")
sqlCopy.ColumnMappings.Add("col17", "預金種目")
sqlCopy.ColumnMappings.Add("col18", "口座番号")
sqlCopy.ColumnMappings.Add("col19", "受取人名")
sqlCopy.ColumnMappings.Add("col20", "業者コード")
sqlCopy.ColumnMappings.Add("col21", "買掛仕入コード")
sqlCopy.ColumnMappings.Add("col22", "買掛経費コード")
sqlCopy.ColumnMappings.Add("col23", "現金コード")
sqlCopy.ColumnMappings.Add("col24", "表示区分")
sqlCopy.ColumnMappings.Add("col25", "削除フラグ")
sqlCopy.ColumnMappings.Add("col26", "更新者")
sqlCopy.ColumnMappings.Add("HANM002INS", "更新日")
'sqlCopy.NotifyAfter = importTbl.Rows.Count
sqlCopy.DestinationTableName = Constant.ShiiresakiMst
sqlCopy.BulkCopyTimeout = 0
sqlCopy.WriteToServer(sourceReader)
End Using
But throw exception: The given ColumnMapping does not match up with any column in the source or destination.
Please help.
Thanks.