If you make the new a_id column (in table B) NOT NULL and provide a default
value (-1 or 0, or anything less than the minimum a_id value), which you can do
while creating the column, then the rows of B that don't correlate to A will
have the default value for a_id.
Then you can use your original plan that worked fine other than the fact that
you caused trouble by making the new column nullable.
alter table B add a_id int not null default -1
Does that help?
At 10:17 AM 10/5/2006, Mark Nicholls wrote
>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.
>[snip]
J. Merrill / Analytical Software Corp
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
