Insert Values in multiple table with foreign key

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

General SQL Server Forums

New to SQL Server Programming

Insert Values in multiple table with foreign key

Author

Topic

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 01:52:30

Hi,

I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.

I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.

I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?

Your reply would be highly appreciated. TIA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 02:02:11

INSERT INTO TableA(fields..)
VALUES(...)

INSERT INTO TableB(FKA,other fields...)
VALUES (IDENT_CURRENT('TableA'),.....)

.....

FKA is foreign key to TableA's field

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 02:24:05

quote:
Originally posted by visakh16

INSERT INTO TableA(fields..)
VALUES(...)

INSERT INTO TableB(FKA,other fields...)
VALUES (IDENT_CURRENT('TableA'),.....)

.....

FKA is foreign key to TableA's field

Thanks. This makes sense when inserting new record. Another question is.. What if i have also TableC which is already populated by value and I need the ID of a certain record from TableC and also will be added with the Second INSERT statement that you provided? How can I perform this insert?

E.g.

---------------------------------------------

TableA
ID-----TASK
1
2
.
.
72-----Prepare company gathering

---------------------------------------------

TableB
ID-----TASKID-----ASSIGNID
1
2
.
.
.
56-----72---------2 << What would be the INSERT statement?

---------------------------------------------

(table C already populated with values)
TableC
ASSIGNID-----NAME
1------------Philip
2------------Sam
3------------Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 02:47:53

quote:
Originally posted by jobejufranz

quote:
Originally posted by visakh16

INSERT INTO TableA(fields..)
VALUES(...)

INSERT INTO TableB(FKA,other fields...)
VALUES (IDENT_CURRENT('TableA'),.....)

.....

FKA is foreign key to TableA's field

Thanks. This makes sense when inserting new record. Another question is.. What if i have also TableC which is already populated by value and I need the ID of a certain record from TableC and also will be added with the Second INSERT statement that you provided? How can I perform this insert?

E.g.

---------------------------------------------

TableA
ID-----TASK
1
2
.
.
72-----Prepare company gathering

---------------------------------------------

TableB
ID-----TASKID-----ASSIGNID
1
2
.
.
.
56-----72---------2 << What would be the INSERT statement?

---------------------------------------------

(table C already populated with values)
TableC
ASSIGNID-----NAME
1------------Philip
2------------Sam
3------------Peter

INSERT INTO TableB(TASKID,ASSIGNID)
VALUES (IDENT_CURRENT('TableA'),IDENT_CURRENT('TableC'))

If you're populating table C before this insert.

Else do below:-

INSERT INTO TableB(TASKID,ASSIGNID)
SELECT IDENT_CURRENT('TableA'),
       ASSIGN_ID
FROM TableC
WHERE NAME='Sam'

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 03:26:29

quote:

INSERT INTO TableB(TASKID,ASSIGNID)
SELECT IDENT_CURRENT('TableA'),
       ASSIGN_ID
FROM TableC
WHERE NAME='Sam'

Should I use VALUES on the above code?

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 03:52:46

quote:
Originally posted by jobejufranz

quote:

INSERT INTO TableB(TASKID,ASSIGNID)
SELECT IDENT_CURRENT('TableA'),
ASSIGN_ID
FROM TableC
WHERE NAME='Sam'

Should I use VALUES on the above code?

Thanks a lot.

You cant if you wanted to insert values from TableC which are prepopulated. However if you just want only the most recently inserted value you can use VALUES

INSERT INTO TableB(TASKID,ASSIGNID)
VALUES (IDENT_CURRENT('TableB'),IDENT_CURRENT('TableC'))

jobejufranz
Starting Member

33 Posts

Posted - 2008-02-28 : 04:33:21

quote:
Originally posted by visakh16

quote:
Originally posted by jobejufranz

quote:

INSERT INTO TableB(TASKID,ASSIGNID)
SELECT IDENT_CURRENT('TableA'),
ASSIGN_ID
FROM TableC
WHERE NAME='Sam'

Should I use VALUES on the above code?

Thanks a lot.

You cant if you wanted to insert values from TableC which are prepopulated. However if you just want only the most recently inserted value you can use VALUES

INSERT INTO TableB(TASKID,ASSIGNID)
VALUES (IDENT_CURRENT('TableB'),IDENT_CURRENT('TableC'))

Thanks a lot visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources