Insert into tables - many to many relationship

[Fibbo]

Pretty new to SQL:

I have the following scenario:
Table1
(TableID int ,
Value1 varchar(50))

Table 2
(Table2ID int ,
Value1 varchar (50))

TableLink
(TableLinkID int,
TableID
Table2ID)

How do I insert a record in tablelink when inserting records in table1. Table2 merely holds data for me?

Would it be some kind of trigger?

[iburyak]

If you are doing insert into Table1 where do you get value for Table2ID in relation with TableID that you trying to insert?

I assume that table TableLink is a multi to multi relationship link and should be filled in separate insert statement when you know which relationships are valid.

[Fibbo]

Hi Iburyak ... thanks for the reply ...let me expand on my explanation.

I need to insert into the Tablelink table a record from table1 (table1ID) with many values from (table2) ... example : USA (value from table1) with (California - value from table2) = row1, next row USA (value from table1) with (Texas) = row2 etc.

This information I'm passing hrough my DAL in VB ...how do insert this information from table1 and table 2 into the tablelink? Hope this makes sense?

[iburyak]

So I have to assume that you are trying to insert one ID from talble1 and all IDs from table2?
What if you try to insert value Canada? No USA states will be valid entries then.

I don't see your logic, sorry.

[almaz]

...This information I'm passing through my DAL in VB ...how do insert this information from table1 and table 2 into the tablelink? Hope this makes sense?

First of all, it looks like you don't need TableLinkID for TableLink table. If it's true (if there are no tables that reference to specific record in TableLink) - I would recommend you to remove it and set primary key to (TableID, Table2ID).

Still not clear what are you trying to ask. I assume that Table1 and Table2 have identity columns and you are using Dataset (or Typed Dataset) and DataAdapter (or TableAdapters for Typed Dataset) as DAL. If no - please describe your DAL.

If you want to add a new record to Table1 and immediately (without saving changes to DB) start adding related items from Table2 (thus inserting records into TableLink):
In this case you will encounter the problem when identity column is not set for Table1 yet, so you cannot insert records into TableLink DataTable (because you don't know TableID for newly-created Table1 record).
Solution - before adding DataRows to TableLink you have to save Table1 changes to DB, and it will automatically update the TableID column of Table1 DataTable to correct value.

If it doesn't answer your question, please describe how do you handle the relations between Table1 and Table2 on client - do you have a TableLink DataTable, or you have some custom collections, or smth. else. Try to submit parts of the code (or some pseudo-code) that will describe what are you trying to achieve.

[iburyak]

Almaz,
you understand questions better then me ..... :)
I am trying to help but not always quite understand what the question is.
This one is really hard to figure…

I will be on vacation Starting February 9 you are on your own here body…. :)
Thank you very much. I learn a lot from you.