Inserting records into 2 tables using 1 Stored Proc

  • Thread starter Thread starter Carl
  • Start date Start date
C

Carl

Hi,

I have 2 Tables. Table1 and Table2.

Table2 holds a foreign key to Table1.

How do I insert my record into Table 1 and then insert 1
or more records into Table2 whose foreign key will be the
identity of the record inserted in Table1?

Any advice or direction to SQL sites is much appreciated.

Thanks,
Carl.
 
There is the function to retrieve the identity. For example
insert into Table1(field2) values('test'2)
insert into Table2(id,field3) values(scope_identity(),'test3')

Somchai
 
Carl:

I hate to ever use this as an answer, but it depends.

1) Where is the key defined? On the database tables?
2) On the local datatables in a dataset?
3) Both/Neither?
If it's #1, Create a DataRelation and let it handle it.
Essentially, the same as #2



Ok, if it's #2 then definitely use a BindingManagerBase
(Francesco Balena's Book on VB.NET and David Sceppa's
Book on ADO.NET [both from MS PRess]) go in to this in
depth. So does John Connell's book on Coding Techniques
(all of which kick a33)

I'd explain a little more, but google on DataRelation
MSDE also discusses it very well, but they write better
than I do.

If it's both, the same as above holds. If neither, plan
on writing a lot of code. If you don't use keyed tables,
nothing will stop you from updating the parent or
children at the Dataset/DataTable level, but your update
will explode, I mean throw an exception. That isn't much
fun. If you don't use either of the two methods above,
you'll need to submit the update to the parent first,
then the second. This takes a lot more code and
increases the probability of an exception. THese two
links should help.

BTW, ADO.NET is about the coolest technology I've
learned, but it definitely takes a different way of
thinking to 'get it'. I've made EVERY mistake you can
make with it and remember what it's like at first.

If you need any more specific help, let me know.

Good Luck,

Bill
W.G. Ryan
(e-mail address removed) (work)
(e-mail address removed) (personel)


http://samples.gotdotnet.com/quickstart/howto/doc/adoplus%
5CRelationalData.aspx

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/vbcon/html/vboriDatasetRelations.asp
 
Back
Top