| Author |
Topic |
|
Jatsie
Starting Member
15 Posts |
 Posted - 2008-11-07 : 07:45:34
|
I am tasked with interfacing two databases but I need exception reporting for records not carried over. The first database is inhouse and the second came with an application we bought. Table layouts differ completely as well as constraints and defaults etc.I have mapped most of the needed fields so all I need is a strategy.I have considered using cursors but I am aware of the risks.I have been trying to find a way with rownumber() function. I need to insert into second database with option to report on records failing.Any help would be greatly appreciated. |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-11-07 : 10:38:30
|
What type of DB is the New and old?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-09 : 02:34:30
|
| Both sql2005 |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-11-09 : 17:04:38
|
If both databases are sql 2005, then there should be no issue in interfacing as long as the column types are properly defined, and your query's are properly written. Also there should be no reason for a cursor. Unfortuantly, it is hard to give a more specific answer since I do not have much information to go off of.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-11 : 04:32:32
|
| SQL update is all or nothing, if one of the records contain incorrect data type, the whole dataset fails. I need to update them in such a way that I can update correct ones, and report on failed ones....This process will happen daily and I need to automate it later. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-11 : 04:57:05
|
| then you have to update it one by one. which might take some time... or use SSIS. i think it has the error capabilities you're looking for._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-11 : 05:05:14
|
| By "one by one" do you mean cursor? Sorry, I am rusty, been out of programming for 4 years. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-11 : 05:11:47
|
| without knowing your database structure there's no way i can say if it needs a cursor._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Jatsie
Starting Member
15 Posts |
Posted - 2008-11-11 : 05:18:39
|
| Nevermind, thanks for the help, i'll just google rownumber() etc and see if I find anything. |
|
|
|
|
database interfacing strategy