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