In Bulk insert how to collecting error giving records

Posted by nageshgatla7-20699 under Sql Server on 2/8/2013 | Points: 10 | Views : 3614 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

Am doing Bulk insert, using while loop. but i want to collect all records which are giving error due to columns datatype mismatch with data which is inserting....for eg writing some code
in below: problem here is when am inserting string value in datetime column at 6 record, it stop executing while loop there itself. but i want to continue while loop even though error ocurs and i want insert all error givng records in some @temp table. pls help me.

declare @a int
set @a=1
DECLARE @temp TABLE(a varchar(2))
BEGIN TRY
WHILE (@a<8)
begin
INSERT INTO Temp4(DateColumn) select dtCreatedDate from Temp3 where strErrorMessage=@a
print 1
set @a=@a+1
-- delete from dbo.Temp4
end
END TRY
BEGIN CATCH

insert into @temp(a) select @a
END CATCH
select * from @temp

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 2/8/2013 [Member] [MVP] Silver | Points: 25

0	Check It Out! Sample Table and Data CREATE TABLE Sample1 ( Id INT IDENTITY(1,1), DateColumn DATETIME ) GO CREATE TABLE Sample1_ErrorRecords ( Id INT , DateColumn VARCHAR(15) ) GO CREATE TABLE SampleRecord ( Id INT IDENTITY(1,1), Column1 VARCHAR(15) ) GO INSERT SampleRecord(Column1) VALUES('2013-02-08') INSERT SampleRecord(Column1) VALUES('2013-02-09') INSERT SampleRecord(Column1) VALUES('Dotnet Funda') INSERT SampleRecord(Column1) VALUES('2013-02-11') INSERT SampleRecord(Column1) VALUES('2013-02-12') GO Inserting/Extracting Error records TRUNCATE TABLE Sample1 TRUNCATE TABLE Sample1_ErrorRecords GO DECLARE @Seq INT, @Count INT, @DateColumn VARCHAR(15) SELECT @Seq =1, @Count = MAX(Id) FROM SampleRecord WHILE(@Seq <= @Count) BEGIN BEGIN TRY SELECT @DateColumn = Column1 FROM SampleRecord WHERE ID = @Seq INSERT Sample1(DateColumn) VALUES(@DateColumn) SELECT @Seq = @Seq +1 END TRY BEGIN CATCH INSERT Sample1_ErrorRecords VALUES(@Seq,@DateColumn) SELECT @Seq = @Seq +1 END CATCH END GO Check the Data SELECT * FROM Sample1 SELECT * FROM Sample1_ErrorRecords GO Result of Sample1 Id DateColumn ---- ---------------------- 1 2013-02-08 00:00:00.000 2 2013-02-09 00:00:00.000 4 2013-02-11 00:00:00.000 5 2013-02-12 00:00:00.000 Result of Sample1_ErrorRecords Id DateColumn ---- ------------------ 3 Dotnet Funda Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions nageshgatla7-20699, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

Sample Table and Data

CREATE TABLE Sample1

(

Id	INT IDENTITY(1,1),

DateColumn DATETIME

)

GO



CREATE TABLE Sample1_ErrorRecords

(

Id	INT ,

DateColumn VARCHAR(15)

)

GO





CREATE TABLE SampleRecord

(

Id	INT IDENTITY(1,1),

Column1 VARCHAR(15)

)

GO



INSERT SampleRecord(Column1) VALUES('2013-02-08') 

INSERT SampleRecord(Column1) VALUES('2013-02-09') 

INSERT SampleRecord(Column1) VALUES('Dotnet Funda') 

INSERT SampleRecord(Column1) VALUES('2013-02-11') 

INSERT SampleRecord(Column1) VALUES('2013-02-12') 

GO

Inserting/Extracting Error records

TRUNCATE TABLE Sample1

TRUNCATE TABLE Sample1_ErrorRecords

GO

DECLARE @Seq INT, @Count INT, @DateColumn VARCHAR(15)

SELECT @Seq =1, @Count = MAX(Id) FROM SampleRecord



WHILE(@Seq <= @Count)

BEGIN

	BEGIN TRY

		SELECT @DateColumn = Column1 FROM SampleRecord WHERE ID = @Seq

		INSERT Sample1(DateColumn) VALUES(@DateColumn)

		SELECT @Seq = @Seq +1

	END TRY

	BEGIN CATCH

		INSERT Sample1_ErrorRecords VALUES(@Seq,@DateColumn)

		SELECT @Seq = @Seq +1		

	END CATCH

END

GO

Check the Data

SELECT * FROM Sample1

SELECT * FROM Sample1_ErrorRecords

GO

Result of Sample1

Id	DateColumn

----    ----------------------

1	2013-02-08 00:00:00.000

2	2013-02-09 00:00:00.000

4	2013-02-11 00:00:00.000

5	2013-02-12 00:00:00.000

Result of Sample1_ErrorRecords

Id	DateColumn

----    ------------------

3	Dotnet Funda

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

nageshgatla7-20699, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts