Insert row into table that have foreign key

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi I have two tables with names Tbl_manager and Tbl_subteam that have relation 1 to n

Tbl_manager Table:
Collapse | Copy Code

UserId uniqueidentifier ---> primary Key
name_manager nvarchar(50)
familli_manager nvarchar(50)
Code_m nvarchar(256)
,...

----------------------------------------
Tbl_SubTeam Table:
Collapse | Copy Code

ID_number int
SubTeam_name nvarchar(50)
SubTeam_Familli nvarchar(50)
Code_m nvarchar(50)
Manage_Id uniqueidentifie ---> foriegn key
,...

my problem is :

how I Could Insert into Tbl_subteam table that is related to tbl_manager?. I tried with follow command , but is wrong :

SQL

INSERT INTO Tbl_subteam(SubTeam_name, SubTeam_Familli, Code_m, Manage_Id)
VALUES(@SubTeam_name,@SubTeam_Familli,@Code_m,IDENT_CURRENT('Tbl_Manager'))
where User_Name=@Username

Posted 26-Nov-13 0:39am

bernova

Add a Solution

Comments

Herman<T>.Instance 26-Nov-13 7:28am

how is your foreign key set up? Does it allow NULL values?

3 solutions

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)

Vishal Pand3y · Answer 1 · 2013-11-26T00:56:00

Solution 1

If You are trying to Insert into 1 table then get any column value and insert it into another table then try to
Create User-Defined Table Types
First Insert into user defined table then get primary key by SCOPE_IDENTITY() and then insert it into another table For More Detail See these Links
User-Defined Table Types
Table-Valued Parameters (Database Engine)
sql-server-table-valued-parameters-in-sql-server
If it doesn't work post here I will post the query

Posted 26-Nov-13 0:56am

Vishal Pand3y

Updated 26-Nov-13 1:02am

v2

Comments

bernova 26-Nov-13 11:19am

tanks a lot
I write two Query :

Query_1 -> ( first I insert value into Tbl_subteam table ) :
INSERT INTO Tbl_subteam (SubTeam_name , SubTeam_Familli, Code_m, birth_date, Sex, proof_tahsil, Address, email, Tel_home, Tel_mobile) VALUES (@SubTeam_name, @SubTeam_Familli, @Code_m, @birth_date, @Sex, @proof_tahsil, @Address, @email, @Tel_home, @Tel_mobile)

//------------------
Then I Update Manage_ID as below:
Query_2 -> (beacause I want to Return Sub team :

UPDATE Tbl_subteam
SET Manage_Id = Tbl_manager.UserId
FROM Tbl_manager INNER JOIN
aspnet_Users ON Tbl_manager.UserId = aspnet_Users.UserId CROSS JOIN
Tbl_subteam
WHERE (aspnet_Users.UserName = @UserName)
--------------------------------------------------------------
if I use two query in SQLCommand C#.net , when I Run in main Server , it cause Reduce Speed for insert or return data in database?

phil.o · Answer 2 · 2013-11-26T01:03:00

You have to create the record in your Manager table before trying to insert a record in your Subteam table that is related to it, if the manager does not exist yet.

Something like:

SQL

DECLARE id uniqueidentifier;
SET @id = NEWID();
INSERT INTO Tbl_manager (UserId, name_manager, famili_manager, Code_m) VALUES (@id, N'Name', N'Family', N'Code');
GO
INSERT INTO Tbl_SubTeam (SubTeam_name, SubTeam_Familli, Code_m, Manage_Id) VALUES (N'MySubteam', N'STFamily', N'MyCode', @id);
GO

Hope this helps.

Tom Marvolo Riddle · Answer 3 · 2013-11-26T01:21:00

Quote:
INSERT INTO Tbl_subteam(SubTeam_name, SubTeam_Familli, Code_m, Manage_Id)
VALUES(@SubTeam_name,@SubTeam_Familli,@Code_m,IDENT_CURRENT('Tbl_Manager'))
where User_Name=@Username

As far as i know,you cannot have a where clause in INSERT statement.You have to update the already existing record.

Use joints to get the ID and update the record.

Insert row into table that have foreign key

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0