Updating tables in sequence with primary key and foreign key relations

Hi all,

       In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations

       Right now my code looks like this

       foreach(object obj in Objects){

       int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table

       int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId

       ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table


      The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.





2/7/2007 9:23:47 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 49


Generally, when you are trying to insert into a set of database table that has foreign key constraints, you have to

1. Insert into parent table.
2. Insert into child table.

As far as I can se, there is no better way than doing this. If you can put the SQL statements together and use multiple SQL statement execution, this might save you a lot of roundtrips.


Kevin Yu
Microsoft Online Community Support

Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
2/9/2007 4:17:07 AM
Hi Kevin Thanks for your reply.
2/9/2007 7:57:51 PM

Similar Artilces:

SQL statement failed, no primary key value for foreign key ... in table ...
Problem: SQL statement failed, no primary key value for foreign key ... in table ... After deleting or changing primary key values in either consolidated or remote database. Question: Are there any triggers that can resolve inserting a record that has a foreign key value with no existing primary key value in primary table? Description: We have a serious problem with the dbremote program. I think it's a general problem that could appear in every database with related tables. Everytime the problem turns up we must create new remote databases from the consolidated datab...

Error: Primary key for row in table 'x' is referenced by foreign key 'f' in table 'y'
(ASA 9 newest ebf) I'm getting this error even though mentioned foreign key has cascade updates (and deletes too) enabled. Is this some new behaviour in asa 9, or why can i get such message for cascaded foreign key? Any ideas? Thanks Pete Found a reason for it...but should it really work so? It's this way: - table a has columns a,b,c - table b has columns a,c, and references columns a,c (with a,c) in table a with cascade updates - table a hes before update trigger to change columns a when column b is changed (set new_row.a = ...) and that set new_row.a = ... i...

Primary Key Prob with SQL table innerjoin with primary key
Hello Exception Details: System.Data.MissingPrimaryKeyException: Table doesn't have a primary key. I have used Stored Procedure in SQL with innerjoin. I think this Stored Procedure is problem because when I use one table with primary key rather using innerjoin to get data from 2 SQL tables I don't have this Table doesn't have a primary key: CREATE proc dbo._BookLink As select Convert(nvarchar(10),BookID)as BookId, CategoryName,Title, Author, Publisher, PublicationDate, ISBN, Price, BriefDesc, FullDesc, [Image], link, htmlready from dbo.BookCategory inner join dbo.B...

Clustered Primary Key and Foreign Key: T-SQL
I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores   (     course...

integer primary/foreign key vs. varchar(255) primary/foreign key
Hello. Is there any principal performance (or some other) difference between using the integer primary/foreign key and using the string (varchar(255)) keys in queries that use tables joins? Server is Sybase SQL Anywhere 5.5.04 Yes! The primary key will only be made up of 12 bytes. By using an integer you ensure that your entire column is in the key. -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX *********************************************************** Enterprise Applications in Your F...

Primary Keys containing common foreign keys from different tables
I have 2 tables Table 1 and Table 2 which have primary keys which include the same foreign key from a third Table 0. Table 1 has a primary key consisting of T1_KEY<pk> and T0_KEY<pk,fk>. Table 2 has a primary key consisting of T2_KEY<pk> and T0_KEY<pk,fk>. Now I want Table 3 to reference both Table 1 and Table 2 so that it has a primary key consisting of: T3_KEY<pk>, T0_KEY<pk, fk>, T1_KEY<pk, fk>, T2_KEY<pk, fk> I am trying to manually reverse engineer a database for which I only have a verbal description. I manage to achieve ...

foreign key as a primary key
hi, I am using a foreign key (which is a primary key in a secondary table) as a primary key in a primary table. There is also another primary key in the primary table. Do You have to declare the foregin key as a primary key ? regards Jan Eikeland email:jaeikela@c2i.net url :http://home.c2i.net/jaeikela In article <368b0234.5056065@forums.powersoft.com>, jaeikela@c2i.net says... > hi, > > I am using a foreign key (which is a primary key in a secondary table) > as a primary key in a primary table. > There is also another primary key in the primary table...

Primary key
Hello, I've got a modeling problem betwin a conceptual model and a physical model and espacially about the foreign key. See below the illustrated exemple of the problem : -Whether the entity � ARTICLE � with two informations : CODE & WORDING -Whether the entity � UNITE � with two informations : CODE & WORDING An article has necesseraly three unity, the unity of command, of storage and of sails. These three unity can be differents. Conceptual model phycical model The generated physical model doesn't suitable me. How ...

AutoUpade UserId [Foreign Key] From the Primary Key UserId in the aspnet_Users table ?
 Hello everyoneI want the userId PrimaryKey in the aspnet_Users Table in the ASPNETDB.mdf  to Update it's foreign key in another table called ClientInfo in the same database, Once the User is created a new userId is created, i want this userId to be automatically entered in the foreign key table ClientInfo...how can that be accomplished?  You can have a trigger on aspnet_Users table doing the insert into the ClientInfo table.Regards,PrashantDont forget to click "Mark as Answer" on the post that helped you.  thanks for the fast reply..it would be great i...

SQL statement failed: (-194) No primary key value for foreign key
I'm getting this error message sporadically when my remote sites are replicating to my central server. The odd thing is the record that is the primary key value does exist! I'm at a lost...Does any have any idea, why this may be happening? Thanks Tasha Did the record exist at the time the error was reported? I have seen cases before where the Parent record is physically sent to the remote *after* the Child record. This would explain why the error was legitimately reported but when you look at the database later the Parent record now exists. -- -------------...

primary key and foreign Key????
 Hi i have 2 table.. Table1UID         Username1               AB2               CD3               DD Table 2ITEM       Price          UIDPOO1      20.00          1POO2      10.00          2POO3       15.00         1 Now i am letting the Admin to delete the user in table1But since UID 1 and 2 are ...

Keys, keys,keys...
Just installed PGP 6.5.8 and all is running fine, so far :-) Question: When I opened the PGP Keys window for the first time, apart from the *Create Key* wizard, there are about 50 odd keys belonging to various and sundry at NAI. Were these supplied for a purpose or only for illustration? Is there any reason they could not or should no be deleted? Question 2: I sent someone an encrypted test message using that person's public key. It went off fine except, I am left with only the encrypted eMail. What setting should I change in order for my eMail client (Outlook 2000) ...

Primary key/Foreign key again
Thanks, Nat and David. Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command to add my primary key: ALTER TABLE Client ADD PRIMARY KEY (SYSID) I got an error: user 'dba' has the row in 'Client' locked. How can I release the locking condition? Click on another table in Sybase Central. Is suspect this is the issue. Dave Wolf Jaguar Product Team John Chyan wrote: > Thanks, Nat and David. > > Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command > to add my primary key: > ...

Primary Key/Foreign Key
Can I use SQL Commands to add Primary key or reference keys to existed tables? (Not a new table, that's why I can't use CREATE TABLE command.) If does, could someone give me some sample codes? Thanks. Hi John, What you need is the ALTER TABLE statement. Look for it in SQLA help for the syntax. Regards, Nat. John Chyan wrote in message <35A4FF5D.5F192806@dissol.com>... >Can I use SQL Commands to add Primary key or reference keys to existed >tables? (Not a new table, that's why I can't use CREATE TABLE command.) > >If does, could someone ...

Web resources about - Updating tables in sequence with primary key and foreign key relations - asp.net.sql-datasource

Relation - Wikipedia, the free encyclopedia
Ternary relation , finitary relation in which the number of places in the relation is three. Ternary relations may also be referred to as 3-adic, ...

Malcolm Turnbull uses shared business background to reset Indonesian relations
Malcolm Turnbull has used his first major overseas visit to reset Australia's fractious relationship with Indonesia, leveraging his common personal ...

Curtis Cheng murder: Marsfield man, 22, arrested in relation to Parramatta killing - DailyTelegraph Search ...
POLICE have charged a third man in relation to the murder of NSW Police Force accountant Curtis Cheng following a vehicle stop in Sydney’s north ...

Angela Merkel and Malcolm Turnbull forge post-Abbott relations during first talks
Malcolm Turnbull and German Chancellor Angela Merkel have discussed submarines, people smuggling, and the humanitarian crisis caused by the Syrian ...

Second man charged in relation to murder of police accountant Curtis Cheng
Police have charged a second man in relation to the murder of police accountant Curtis Cheng, who was killed in a shooting in Parramatta last ...

Scott Morrison's department is urging him to tackle workplace relations reform
Treasury is urging Scott Morrison to embark on major workplace relations reform, pointing to a report that recommend cuts to penalty rates as ...

Cabinet document reveals Turnbull Government under pressure to change industrial relations laws
The Turnbull Government is being pushed to change Australia's workplace relations system, a document obtained by the ABC reveals.

China Praises Trudeau's Father For His 'Extraordinary Vision' In Establishing Relations
Xi Jinping told Canada's current prime minister that China will never forget Pierre Elliott Trudeau's decision to open diplomatic relations. ...

Relations with Japan thawing but not yet warm enough
Relations with Japan thawing but not yet warm enough

Mizzou's New President Made 'Race-Relations' Video With Activist
INSIDE JOB: Mizzou's New Interim President Made 'Race-Relations' Video With Activist Leader

Resources last updated: 12/6/2015 2:40:15 AM