Write the Stored Procedure To Insert The Data in All the 3 tables ?

Posted by Kasani007 under Sql Server on 8/8/2014 | Points: 10 | Views : 1555 | Status : [Member] | Replies : 6
Write the Stored Procedure To Insert The Data in All the 3 tables ?

I have 3 tables Customer, Product And Sales Tables With Columns corresponding constraints as below:

Customer Table:
---------------
CustomerID int PrimaryKey
CustomerName Varchar

Product Table:
--------------
ProductID int identity PrimaryKey
ProductName varchar
Price decimal
Quantity int

Sales Table:
------------
SalesID int identity PrimaryKey
CustomerID int ForiegnKey
Quantity int




Responses

Posted by: Manimaddu on: 8/8/2014 [Member] Starter | Points: 25

Up
0
Down
Hi...
Write all insert queries in one stored procedure..
If You want an example see the below link.

http://stackoverflow.com/questions/20963961/sql-server-stored-procedure-to-insert-in-multiple-tables

Thanks & Regards,
Mani Kumar

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ajay.Kalol on: 8/8/2014 [Member] Starter | Points: 25

Up
0
Down
CREATE PROCEDURE SPName
--- Parameters wll goes here
--- @param1 VARCHAR(100)
AS
BEGIN

INSERT INTO CutomerTable(CutomerID, Customername)
VALUES ('ID','Name')

DECLARE @CustomerID INT = SCOPE_IDENTITY()

INSERT INTO ProductTable(ProductID, ProductName, Price, Quantiry)
VALUES ('ID','Name',10,10)

INSERT INTO Salestable(SalesID, CutomerID, Quantiry)
VALUES ('ID',@CustomerID,10)

END
GO


Ajay
ajaypatelfromsanthal.blogspot.in

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kasani007 on: 8/8/2014 [Member] Starter | Points: 25

Up
0
Down
thanQ...If I have a foreign key for Quantity also,then two foreign keys will be in Sales table, then how we can set SCOPE_IDENTITY() for both CustomerID and Quantity in Sales Table.

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/8/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You can use IDENT_CURRENT('tablename') to get the recent auto-incremented value of a table...
the SCOPE_IDENTITY() function will returns recent IDENTITY value which incremented irrespective of TABLE....

So use IDENT_CURRENT('Customer') to get latest customer ID and IDENT_CURRENT('Sales') to get recently added identity value....

NOTE: you must have IDENTITY column in a table to use IDENT_CURRENT(); otherwise it will return NULL value


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark as Answer if got clear idea/solution...

Post us back the issue/doubts if not resolved the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

If you want to insert the data into 3 tables using single procedure then you should wrote 3 insert statements.

EX:
create procedure Insert_Proc
(

@Param1 datatype,
@Param2 datatype
)
as

begin
insert into table1(col1,col2)
values(@Param1,@Param2)

insert into table2(col1,col2)
values(@Param1,@Param2)

insert into table3(col1,col2)
values(@Param1,@Param2)
end


Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response