It was a long rambling one, even for me.....I don't blame you.. I can't quite see what I could put into the temp table that would help.....I do not know the final @identity's until it's in the destination table, and I have no way of correlating the final data back into the source.
-----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Frans Bouma Sent: 05 October 2006 16:32 To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM Subject: Re: [ADVANCED-DOTNET] offish topic question about ms sql.... > 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. I'd use a temptable. Insert the data in there in such a form so it's easy to read and insert into the final destination. But I admit, I didn't fully read the whole post in such depth that I fully grasp the problem as a whole. FB > > -----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 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
