In Bulk insert how to collecting error giving records

Posted by nageshgatla7-20699 under Sql Server on 2/8/2013 | Points: 10 | Views : 2568 | Status : [Member] | Replies : 1
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




Responses

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

Up
0
Down
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

Login to post response