October 10, 2008 at 4:09 pm
Hi,
I have this task of importing some huge tables from mysql into sql server 2005 and one of the burdens is to check the validity of date/datetime fields and null-ify them in case they're out of order (for instance, mysql accepts dates like 2008-02-30 or 2005-06-31, go figure!). I'm using the SSIS to do the import and one of the ways to solve the problem would have been to attach a SSIS expression in a derived column object.
The other way would be to construct a table trigger the fires per statement but does the job per row. Only the SQL Server ain't Oracle and it has no "for each row" and "before" triggers.
Any ideas how can I have this done?
Thanks a bunch.
October 10, 2008 at 4:15 pm
You can implement functionality like a BEFORE trigger using SQL Server's INSTEAD OF triggers. The main difference is that the BEFORE trigger will go ahead and do the insert, whereas you need to explicitly reissue the INSERT statement in the INSTEAD OF trigger for the operation to actually happen.
That being said - I don't think the trigger will ever fire, since the operation will likely fail before the insert is even attempted. Your SSIS expression (or for that matter, just using the ON ERROR behavior of SSIS data flow tasks) is going to be your best bet. PErsonally - I'd use the error handling in SSIS (do the import, and when it fails, send those rows to another data flow task that does the inserts but without that column.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 14, 2008 at 9:05 am
I agree with Matt. I'd use SSIS to fix the data, that's part of what it is designed to do. I am doing that when loading data from FoxPro to SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 6:09 am
Hi Daniel,
Just an idea, but why don't you first fix those dates/datetimes in MySql BEFORE you try to transfer the data to SQL? That's of course assuming that you can identify all the incorrect ones in the first place.
Regards,
Jan
October 15, 2008 at 9:02 am
MySQL is the last place where I'd try to fix these dates.
How can you fix invalid dates like 2008-02-30 or 2005-06-31 in there? MySQL accepts those dates with a warning. I can instruct to return an error having MySQL in "strict mode" but that means the whole application is going break down. So the only solution was to run separate insert selects with case statements on the SQL Server side with ISDATE inside that tests all the date fields. BUT I'm not gonna write separate statements forever to import each and every table manually - not to mention that those insert selects are using a linked object + an ODBC connection which together are slow, in comparison with SSIS which is using an ADODB connection to my knowledge - way faster.
Right now, in MySQL side, I'm using case statements to replace the 0 date entries with NULLs - looks like our developers have a real desire to use "0000-00-00 00:00:00" instead of a NULL for the "unknown" domain - they're defaulting to 0 in table definition. SQL Server doesn't accept 0 dates in a table unless I explicitly tell it so - nor I am interested to have them around.
I hope this answers your curiosity.
May 5, 2009 at 9:36 am
hi i need an identity column value before inserting record in sql server 2000
how can i
pls help me
May 5, 2009 at 2:24 pm
Hi Nagendra,
Why do you add your question to a thread that's been dead for more than six months? And then on a totally differemt topic?
Just to help you on the way, look up IDENTITY in SQL Books On-line (or BOL for short). If that doesn't get you anywhere I'd suggest you start a new thread in the SQL 2000/T-SQL or SQL 2000/General forum.
Regards,
Jan
May 5, 2009 at 2:31 pm
Just realized, I may have misunderstood you. Did you mean you need to know beforehand what the identity number will be that is assigned to the row you are about to insert? Don't even attempt that.
You may want to have a look at @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in BOL as well, which will tell you AFTER the insert what the IDENTITY value is. Just be aware that they each behave slightly differently, i.e. read the fine print as well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply