Sql Server中的批量插入错误 [英] Bulk Insert Error in Sql Server
本文介绍了Sql Server中的批量插入错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨
我在Sql批量插入时收到以下异常。
我收到此错误
Hi
I am getting the following exception in Sql in bulk Insert.
I ma getting this error in
sqlCmd.ExecuteNonQuery();
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Stroed程序是：
The Stroed Procedure is :
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[PSGTMS].[tmsp_CustomerContactInsert_CN]') AND type in (N'P', N'PC'))
DROP PROCEDURE [PSGTMS].[tmsp_CustomerContactInsert_CN]
GO
--Exec PSGTMS.tmsp_CustomerContactInsert_CN '\\server65\CONBULKINSERT\CustomerContact.CSV',0
CREATE PROCEDURE [PSGTMS].[tmsp_CustomerContactInsert_CN]
(
@filename VARCHAR(255),
@retval INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @stmt NVARCHAR(MAX)
SET @retval = 1
IF OBJECT_ID('tempdb.dbo.#CustomerContact_CN') is not null
BEGIN
DROP TABLE #CustomerContact_CN
END
CREATE TABLE #CustomerContact_CN (
CustomerNumber CHAR(10),
LastName VARCHAR(30),
FirstName VARCHAR(15),
Telephone VARCHAR(16),
Extension VARCHAR(6),
Fax VARCHAR(16),
EmailID CHAR(63),
[TYPE] CHAR(3)
)
SET @stmt = 'BULK INSERT #CustomerContact_CN FROM ' + '''' + @filename + '''' +
'WITH
(
DATAFILETYPE =''widenative'',
CODEPAGE = ''RAW'',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)'
EXEC SP_EXECUTESQL @stmt
BEGIN TRANSACTION
INSERT INTO PSGAR.ARCustomerContact1
(
CustomerNo,
LastName,
FirstName,
Telephone,
Extension,
Fax,
IN_EmailId,
[Source]
)
SELECT
C.CustomerNumber,
ISNULL(C.LastName,''),
ISNULL(C.FirstName,''),
ISNULL(C.Telephone,''),
ISNULL(C.Extension,''),
ISNULL(C.Fax,''),
ISNULL(C.EmailID,''),
ISNULL(C.[TYPE],'')
FROM #CustomerContact_CN C WITH(NOLOCK)
IF @@ERROR <> 0
BEGIN
SET @retval =0
ROLLBACK TRANSACTION
RETURN
END
SET @retval =1
COMMIT TRANSACTION
SET NOCOUNT OFF
END
样本CSV数据为：
The Sample CSV Data is:
000006 ,Payable,Accounts,3368012065,,9058126177,,AP
000006 ,McPherson,Peter,9058126084,,9058126018,,AP
000006 ,Payable,Accounts,,,6042640252,,AP
000008 ,Drunsic,Stephen,8023621516,7,,sdrunsic@nerr.com,EBL
000008 ,Scarlotta,Christine,8023621516,3,8023621918,,AP
000027 ,Freitas,Susan,,,9058791123,susan@vaughanwood.com,AP
000033 ,Cuthbertson,Gayle,8016492090,,,gaylec@energysolutions.com,EBL
000033 ,Olson,Milo,8016492274,,,molson@energysolutions.com,EBL
000042 ,Shivji,Hafiz,4032796267,101,,accounting@classicfoodsltd.com,EBL
000042 ,Payable,Accounts,,,4032796515,accounting@classicfoodsltd.com,AP
000042 ,SHIVJI,ABID,,,4032796515,,AP
000043 ,Clausen,Larry,7084582626,,,larry@combinedwarehouse.com,EBL
000043 ,Payable,Accounts,,,7084582691,,AP
000043 ,Castaldo,Skip,,,7084582691,skip@combinedwarehouse.com,AP
000045 ,Payable,Accounts,,,4032123697,,AP
000045 ,Payable,Accounts,4036404230,,4032123724,,AP
000046 ,Stelter,Rich,,,7084964158,,AP
000046 ,Payable,Accounts,7084962485,,7084964158,,AP
000049 ,Terrio,Sandra,5146316000,,,sterrio@advantex.ca,EBL
000049 ,Richard,Mike,5146316000,,,mrichard@advantex.ca,EBL
推荐答案
在此找到您的问题
http://stackoverflow.com/questions/8513943/sql-server-the-bulk-load-failed-the-column-is-too-long-in-the-data-file -for-ro [ ^ ]
Find Your Problem on this
http://stackoverflow.com/questions/8513943/sql-server-the-bulk-load-failed-the-column-is-too-long-in-the-data-file-for-ro[^]
这篇关于Sql Server中的批量插入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
