May 6, 2014 at 6:48 am
we are migrating ms access 2007 databases to sql server 2008r2 and we are getting invalid data format error in data columns between access and sql and we want to run SSIS package and bring in bad date data and keep in separate table in the database so we know what that bad data are. here is our script and needs some help.
insert Into dbo.BadDataTable
from [SourceTable]
where (isdate([Req Date]) = 0 and [Req Date] is not null)
or (isdate([Rcd Fiscal] ) = 0 and [Rcd Fiscal] is not null)
or (isdate([Signed SCH] ) = 0 and [Signed SCH] is not null)
or (isdate([Order Placed] ) = 0 and [Order Placed] is not null)
or (isdate([Delivery Date] ) = 0 and [Delivery Date] is not null)
or (isdate([Received Date]) = 0 and [Received Date] is not null)
Delete from SouceTable
where ID in (Select ID from BadDataTable)
Go
May 6, 2014 at 7:38 am
Nassan (5/6/2014)
we are migrating ms access 2007 databases to sql server 2008r2 and we are getting invalid data format error in data columns between access and sql and we want to run SSIS package and bring in bad date data and keep in separate table in the database so we know what that bad data are. here is our script and needs some help.insert Into dbo.BadDataTable
from [SourceTable]
where (isdate([Req Date]) = 0 and [Req Date] is not null)
or (isdate([Rcd Fiscal] ) = 0 and [Rcd Fiscal] is not null)
or (isdate([Signed SCH] ) = 0 and [Signed SCH] is not null)
or (isdate([Order Placed] ) = 0 and [Order Placed] is not null)
or (isdate([Delivery Date] ) = 0 and [Delivery Date] is not null)
or (isdate([Received Date]) = 0 and [Received Date] is not null)
Delete from SouceTable
where ID in (Select ID from BadDataTable)
Go
This seems overly complicated to me. If you are checking if a value returns 0 from IsDate there is no need to also check it for not null. You could skip the BadDataTable entirely and just delete the rows.
DELETE
from [SourceTable]
where isdate([Req Date]) = 0
or isdate([Rcd Fiscal] ) = 0
or isdate([Signed SCH] ) = 0
or isdate([Order Placed] ) = 0
or isdate([Delivery Date] ) = 0
or isdate([Received Date]) = 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply