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