how to insert record in two tables having primary n foreign key relationship

Question

1.00/5 (1 vote)

See more:

I have two tables.. I want to insert records in tables by transaction using stored procedure.
there is primary and foreign key relationship between tables.. product_id is field of one table. it is primary key and auto incremented. so how I insert product_id in second table which is foreign key of table..
So plz help me..
thanks in advance..

Posted 9-Nov-14 21:55pm

heta.dave

Updated 9-Nov-14 21:59pm

v2

Add a Solution

Comments

Shweta N Mishra 10-Nov-14 3:55am

Did you tried to insert the data in both table in same transaction ?

Thanks7872 10-Nov-14 3:57am

Have you tried :

insert into first_table.....
insert into second_table....?

heta.dave 10-Nov-14 4:03am

Thanks for your valuable time..
I didn't get product_id for second_table.. it is auto identity in first table.So how to get product_id for second_table..

Maciej Los 10-Nov-14 4:05am

Your question is not clear. Please provide more details about your requirements.

heta.dave 10-Nov-14 4:11am

table name: Products
Cols: ProductID, ProductName, ProductDescription

table: ProductImages:
cols: ProductImageID, ProductID, ProductImageName

product table has productid which is auto incremented n also primary key.. It is auto incremented in table.. but how get that value of productID in productImages table

3 solutions

Solution 1

Insert first into the table having primary key
and then insert in second table having foreign key.

Posted 9-Nov-14 22:03pm

Shweta N Mishra

Solution 2

Let's imagine this scenario:
1) add record to Main table ad get primary key value

SQL

CREATE PROCEDURE InsertToMain
    @<value_list>
AS
BEGIN 
    INSERT INTO MainTable(<field_list>)
    VAULES(<value_list>)

    RETURN @@IDENTITY
END

2) when you added value to Main table, you're able to add values to child table

For further information, please see:
@@IDENTITY (Transact-SQL)[^]
Walkthrough: Using Only Stored Procedures (C#)[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]
Try. Good luck!

Posted 9-Nov-14 22:15pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arora_Ankit · Accepted Answer · 2014-11-09T23:13:00

Solution 3

SQL

CREATE PROCEDURE InsertData
@Param1	int,     --Main Table
@Param2	varchar(10),--Main Table
@Param3	varchar(300),--Child Table
@Param4	char(4)--Child Table
AS
BEGIN
	DECLARE @pkMain int

	INSERT INTO MainTable (Col1, col2)
		VALUES (@Param1, @Param2);

	SELECT
		@pkMain = SCOPE_IDENTITY();

	INSERT INTO ChildTable (col1, col2, col3)
		VALUES (@pkMain, @Param3, @Param4);

END

Posted 9-Nov-14 23:13pm

Arora_Ankit

Comments

heta.dave 10-Nov-14 5:17am

It works..
thanks a lot..

Arora_Ankit 10-Nov-14 5:18am

you are welcome:)