Originally I was going to try to do ii) but because I couldn't see a way of correlating the data back, I abandoned it and went for the ugly yet simple way of appending the foreign key in the destination table.....now I've been bitten.
The problem is not orphans, the problem is correlating the destination table and the source table after the insert...no correlation seem exists.... I agree with the concept of a simple A_B table with 2 rows, but there seems to be no easy way to actually insert the data into that row...... Though I have just seen a similar problem elsewhere..... http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9831겻 declare @LastID int -- declare @tableA table( increment int identity(1,1), fieldA1 <fieldA1 type>, fieldA2 <fieldA2 type>, fieldA3 <fieldA3 type>) -- select @LastID = max(fieldB1) from tableB -- insert into @tableA(fieldA1, fieldA2, FieldA3) select * from tableA -- set identity_insert tableB on -- insert into tableB(fieldB1, fieldB2) select @LastID + increment, fieldA1 from @tableA -- if @@error <> 0 begin set identity_insert tableB off return <error code> end -- set identity_insert tableB off -- insert into tableC(...) select @lastID + increment, fieldA2, fieldA3 from @tableA i.e. turn auto increment off.....create your own id's in a temp table and then these id's to correlate via @lastID + increment---->increment !!!!! Seems pretty horrific....if I even understand it!. It would obviously need to be in a transaction!... -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Peter Ritchie Sent: 05 October 2006 17:29 To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM Subject: Re: [ADVANCED-DOTNET] offish topic question about ms sql.... When I'm designing tables, I try and decouple unrelated data by separating them into two or more tables. This is making the schema more normalized. In the case where you've already deployed a database with a given schema, I would further laud keeping the data in separate tables (your option ii). This ensures that database schema will work with multiple versions of the app, as it's not much fun (possible?) trying to update all the clients that access one remote SQL Server all at the same time. Foreign keys and triggers can be defined (I believe Visio does this for you automatically) to validate data upon insert and ensure no orphans in the newly added table. I've got many tables that simply contain two columns: two ids that link a row in one table with the row in another (one-to-one relationship)-- without having any direct dependency of one table to the other. =================================== 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
