Capture error rows

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  January 12, 2010 at 8:38 am

  #219143

  I am trying to insert 4 rows from #Dev to #QA, out of which 2 rows will fail converting

  from varchar to datetime.

  What i am trying to acheive is, the rows that will be converted to datetime should be inserted to #QA and the other error rows should be logged to #catch table.

  This has to be done using T-SQL (not SSIS).

  The below code is not working obviously. Any ideas please.

  Thanks in advance.

  create table #Dev (PName varchar(10), Rec_Dt varchar(8))

  insert into #Dev values ('Absfsg','20090101')

  insert into #Dev values ('Axdfere','20090102')

  insert into #Dev values ('Error1','2009 010')

  insert into #Dev values ('Error','axxcfffs')

  --select * from #Table

  create table #QA(PName varchar(10), Rec_Dt datetime)

  begin try

  insert into #QA

  select * from #Dev

  end try

  begin catch

  select * into #Catch

  from #Dev

  end catch

• WayneS

  SSC Guru

  Points: 95461

  More actions

  January 12, 2010 at 8:59 am

  #1102500

  UnionAll (1/12/2010)

  ---

  I am trying to insert 4 rows from #Dev to #QA, out of which 2 rows will fail converting

  from varchar to datetime.

  What i am trying to acheive is, the rows that will be converted to datetime should be inserted to #QA and the other error rows should be logged to #catch table.

  This has to be done using T-SQL (not SSIS).

  The below code is not working obviously. Any ideas please.

  Thanks in advance.

  You need to use the IsDate() function:

  create table #Dev (PName varchar(10), Rec_Dt varchar(8))

  insert into #Dev values ('Absfsg','20090101')

  insert into #Dev values ('Axdfere','20090102')

  insert into #Dev values ('Error1','2009 010')

  insert into #Dev values ('Error','axxcfffs')

  --select * from #Table

  create table #QA(PName varchar(10), Rec_Dt datetime)

  --begin try

  insert into #QA

  select * from #Dev where ISDATE(Rec_dt) = 1

  --end try

  --begin catch

  select * into #Catch

  from #Dev where ISDATE(rec_dt) = 0

  --end catch

  select * from #QA

  select * from #Catch

  DROP TABLE #Dev

  DROP TABLE #QA

  DROP TABLE #Catch

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2