February 5, 2009 at 2:51 am
Hi there,
i want to insert data in multiple tables through stored procedure.
but my problem is i want to insert the identity key of the first table into the second table as a foreign key.
can anyone solve this problem.
i am using SQL server 2008.
Thank's
AP
February 5, 2009 at 3:21 am
If i understand your question correctly, make the primary key of the first table as a foreign key in the second table , PK-FK relationship maintains the data integrity and this relationship means they cannot have different values.
February 5, 2009 at 5:12 am
[font="Verdana"]something like:
Create Table Tab1
(IdInt Identity(1,1),
NameVarChar(10))
Go
Create Table Tab2
(IdInt,
NameVarChar(10))
Go
-- Insert
Insert IntoTab1 (Name)
Output Inserted.Id, Inserted.Name Into Tab2(Id, Name)
Values ('AAAA')
Insert IntoTab1 (Name)
Output Inserted.Id, Inserted.Name Into Tab2(Id, Name)
Values ('BBBB')
Go
Select * From Tab1
Select * From Tab2
Go
Drop Table Tab1
Drop Table Tab2
Go
but the problem with this is that you can not add any constraint, i.e. PK/FK, in table.
better you go for SCOPE_IDENTITY(). Fro more information refer BOL.
Mahesh
[/font]
MH-09-AM-8694
February 5, 2009 at 5:51 am
The last post is close. You do want to use the OUTPUT clause. But instead of trying to use it one row at a time, which is likely to be VERY problematic. This is straight out of BooksOnline:
USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
While I would probably use a temp table instead of a table variable, you can simply select from the temporary table as part of the next INSERT statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 7:26 am
[font="Verdana"]
Grant Fritchey (2/5/2009)
The last post is close...
Grant i didn't get you.
Mahesh
[/font]
MH-09-AM-8694
February 5, 2009 at 7:34 am
Mahesh Bote (2/5/2009)
[font="Verdana"]Grant Fritchey (2/5/2009)
The last post is close...Grant i didn't get you.
Mahesh
[/font]
Your post is close to the answer. The thing is, SCOPE_IDENTITY will only work for a single row insert. If you need multiple rows, you should use something like the BOL example. Unlike what you posted, you can certainly support PK & FK constraints using the data from the OUTPUT clause. That's explicitly what it's doing for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 12:32 pm
Hi
You need to use SCOPE_IDENTITY().
I belive you want to insert data first into Parent Table and then use the Identity value and insert it in Child table. Repeate the above steps for all the records.
Incase if you want to insert the data into Parent table first for all the reocords and then to the child records then it will not work.
Thanks -- Vj
February 5, 2009 at 12:59 pm
SCOPE_IDENTITY will work, but only if you ever insert a single row. If you insert two or more rows, SCOPE_IDENTITY won't adequately cover the situation. That's what the OUTPUT clause is for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 10:41 pm
Hi all,
what i want to do is there are two tables
1. Employee
1.1 Empid
1.2 Firstname
1.3 Lastname
1.4 Designation
1.5 Dateofjoining
2. Login
2.1 LogId
2.2 Empid
2.3 Username
2.4 Password
2.5 SecurityQuestion
2.6 SecurityAnswer
now what i want to do is i need to insert data first in employee table and after that the empid (pk of employee table) which i want to insert into the login table along with the fields 2.1,2.3,2.4,2.5,2.6 etc.
thanks
AP
February 6, 2009 at 2:50 am
[font="Verdana"]Then what you need to do is, to use the Table variable example which Grant has suggested.
Mahesh[/font]
MH-09-AM-8694
February 9, 2009 at 11:32 pm
guys this is my store procedure for abt which i am talking.
-- Creates a new record in the [dbo].[Employee] table.
ALTER PROCEDURE EmployeeAdd
@p_FName varchar(25),
@p_MName varchar(25),
@p_LName varchar(25),
@p_DOB nvarchar(10),
@p_Designation int,
@p_BasicSalary real,
@p_Address varchar(255),
@p_MobileNo varchar(10),
@p_PhoneNo varchar(15),
@p_EmailID varchar(25),
@p_Type varchar(10),
@p_DOJ datetime,
@p_DOT datetime,
@p_ROT varchar(255),
@p_EmployeeId_out int output,
@p_UserName varchar(10),
@p_Password varchar(8),
@p_SecurityQuestion varchar(100),
@p_SecurityAnswer varchar(10)
AS
BEGIN
INSERT
INTO [dbo].[Employee]
(
[FName],
[MName],
[LName],
[DOB],
[Designation],
[BasicSalary],
[Address],
[MobileNo],
[PhoneNo],
[EmailID],
[Type],
[DOJ],
[DOT],
[ROT]
)
VALUES
(
@p_FName,
@p_MName,
@p_LName,
@p_DOB,
@p_Designation,
@p_BasicSalary,
@p_Address,
@p_MobileNo,
@p_PhoneNo,
@p_EmailID,
@p_Type,
@p_DOJ,
@p_DOT,
@p_ROT
)
SET @p_EmployeeId_out = SCOPE_IDENTITY()
INSERT
INTO [dbo].[Login]
(
[EmployeeId],
[UserName],
[Password],
[SecurityQuestion],
[SecurityAnswer]
)
VALUES
(
@p_EmployeeId_out,
@p_UserName,
@p_Password,
@p_SecurityQuestion,
@p_SecurityAnswer
)
END
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply