A challenging insert between two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A challenging insert between two tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-24 : 08:04:52

Darren writes "This insert has gotten me and my colleagues scratching our heads.

I have in the following two tables:


Table A
TID    SID
 1       5
 2       5

TABLE B
TID    Bracket     Fee
 1        1         50
 1        2         30
 2        1         40

Table A has a primary key of (TID)
Table B has a primary key of (TID, Bracket)
There is a foreign key from B -> A on the TID column.
The TID column in Table A is an identity column with an increment of 1.

I want to insert a rows into table A with a new SID. Then insert new rows into table B with the new TID but with the same Bracket and Fee of the original values.

I.E. The tables should look like this after inserting:


TABLE A
TID    SID
 1       5
 2       5
 3       6
 4       6

TABLE B
TID    Bracket     Fee
 1        1         50
 1        2         30
 2        1         40
 3        1         50
 3        2         30
 4        1         40

I can insert into table A fine. I know the first and second SID values.
How can I then insert into Table B using only ONE insert statement??
It's harder than it looks!"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 08:22:15

1. How is SID 6 determined to be "new" ? How do we know SID 5 is old ? do we always insert the MAX(SID) from Table A?

2. Why does TID 3 turn into those 2 rows in Table B ? I see that they are duplicated from what is already in TableB for TID 1, but how does TID 3 relate to TID 1 ? Please don't say based on the order they exist or were inserted into the table !

Show us what happens when more values are added to TABLE A and another INSERT is required. Your requirements as presented are very ambigious (to me at least).

The EXACT one-time INSERT to perform based on the data you have given to insert the data for MAX(SID) from Table A to Table B is really easy, but I have a feeling there are many hidden requirement and other situations we are not aware of .

- Jeff

airjrdn
Starting Member

35 Posts

Posted - 2003-09-24 : 17:45:34

I'm not 100% sure of what you are trying to do. Could you spell it out a with a little more detail?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources