The idea is it's merging data from one system into another....the 'slave' knows nothing about the source data...it references table B.....so the data needs to get into B...
I've obviously simplified the example...not only does it need to track what's been inserted, but potentially update data that has previously been transferred, therefore there needs to be some mechanism to correlate the target data B, with updates to the source data A. -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of simon wheeldon Sent: 05 October 2006 15:45 To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM Subject: Re: [ADVANCED-DOTNET] offish topic question about ms sql.... Does the data actually need to exist in table B? Could you add a flag field in table A which is set to indicate data that would have otherwise been copied to table B. Then, do a union query of B and the records in A with the flag set as true. Difficult to tell without knowing how your app relies upon table A and B, but a potential solution nonetheless. Cheers, Simon -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Mark Nicholls Sent: 05 October 2006 15:18 To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM Subject: [ADVANCED-DOTNET] offish topic question about ms sql.... please ignore me and tell me to go away....it's difficult to find a forum where you get intelligent and sensible answers.....fawn, fawn. (though it is sort of associated to dataadapters). this ones about potentially about MSSQL! consider a table with data in it..and a unique id.... table A a_id int data nvarchar(100) and another similar table table B b_id int data nvarchar(100) you want to insert data into B, that you've never inserted before....now you would be tempted to add a new foreign key to B so table B b_id int data nvarchar(100) a_id int NULL and then go INSERT INTO B (...) SELECT (...) FROM A WHERE A.a_id not in (SELECT B.a_id FROM B)... fine...works a treat....the problem is adding the NULLABLE field....it potentially breaks applications....it shouldn't in well written apps, but I have a C# app that seems to read the data from B using explicit field names ("SELECT b_id,data FROM B"), and then (stupidly) writes the data back using "Select * FROM B" as the select command in the dataadapter writing back (with no explicit update command), the dataadapter complains (sensibly) that you are trying to write data into a field B.a_id when no corresponding source field exists. answers.... i) rewrite the code properly....hmmm it's a big 3rd party written app...not good, rather not go there. ii) do the sql in such a manner that doesn't require the addition of a_id to table B. Thinking about option ii).....it would seem to be sensible to have a new table table A_B a_id int b_id int that links A and B.....and then all we need to do is INSERT INTO B (...) SELECT (...) FROM A WHERE A.a_id not in (SELECT A_B.a_id FROM A_B)... now we need to update A_B with the new records but we can't!!!!!!...because there is no way to correlate the data inserted in B with the data inserted in A.... I need to be able to go.... INSERT INTO B (...) SELECT (...) FROM A WHERE A.a_id not in (SELECT A_B.a_id FROM A_B)...AND INTO A_B (A_ID,[EMAIL PROTECTED]) or something similar.....no such construct seems to exist. or cursors?....ooo...nasty...never used them. =================================== This list is hosted by DevelopMentorR http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com *************************************************************************** CONFIDENTIALITY NOTICE The contents of this e-mail are confidential to the ordinary user of the e-mail address to which it was addressed, and may also be privileged. If you are not the addressee of this e-mail you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever.If you have received this e-mail in error, please e-mail the sender by replying to this message. It is your responsibility to carry out appropriate virus and other checks to ensure that this message and any attachments do not affect your systems / data. Any views or opinions expressed in this e-mail are solely those of the author and do not necessarily represent those of MTV Networks Europe unless specifically stated, nor does this message form any part of any contract unless so stated. MTV reserves the right to monitor e-mail communications from external/internal sources for the purposes of ensuring correct and appropriate use of MTV communication equipment. MTV Networks Europe *************************************************************************** =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
