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 : 1338 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

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

0	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

0	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

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

Latest Posts