Insert inside a transaction

Last Post 10 Mar 2008 07:10 AM by SwePeso. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
kjmcsd
New Member
New Member

--
05 Mar 2008 01:32 PM
OK lets say you have

Begin trans

insert into tableA (HouseID, Name, Descr) values (123, "a", "house")

insert into tableB (HouseDetailsID, HouseID, Descr) values (485, 123, "123 Main St.")

insert into tableB (HouseDetailsID, HouseID, Descr) values (485, 124, "123 Main St.")
--HouseID has a FK constraint

Commit

End Trans


When I run this I get an error on the third insert:
The INSERT statement conflicted with the FOREIGN KEY constraint

I know why because a HouseID is not in tableA. But which one?

Do I have to end up doing something like this?

Begin trans
Begin trans
insert into tableA (HouseID, Name, Descr) values (123, "a", "house")
Commit

Begin trans
insert into tableB (HouseDetailsID, HouseID, Descr) values (485, 123, "123 Main St.")
Commit

Begin trans
insert into tableB (HouseDetailsID, HouseID, Descr) values (485, 124, "123 Main St.")
Commit

Commit

kjmcsd
New Member
New Member

--
06 Mar 2008 05:03 AM
Thanks! Indirectly you answered my question.
SQLUSA
New Member
New Member

--
06 Mar 2008 09:53 PM
In SQL Server 2008, you can replace the last 2 inserts with just one! Neat feature for case like you have.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC - March 17-20: http://www.sqlusa.com/
SwePeso
New Member
New Member

--
10 Mar 2008 07:10 AM
You are not authorized to post a reply.

Acceptable Use Policy