> 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® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
