How to insert data into two diffrent tables using sp in sql

Question

hi all, i want to insert data into two diffrent tables based on Id using stored procedure in sql server please provide soultion regarding this, Thanks

Ankur Mistry · Answer

check : https://stackoverflow.com/questions/5545754/insert-with-the-same-data-in-different-tables

Laxmidhar Sahoo · Answer

Hi I sent the details step 1 - Create a table with the below code create table test2 ( ID int identity(1,1), Salary decimal(18,2) null, Ecode varchar(50) null ) step 2 - create send table with the below code create table test2 ( ID int identity(1,1), Salary decimal(18,2) null, Ecode varchar(50) null ) step 3 - create a procedure with the below code create procedure Insert_tables ( @Ename varchar(50), @Ecode varchar(50), @salary decimal(18,2) ) as Begin begin try Insert into test1(Ename,Ecode)values(@Ename,@Ecode); Insert into Test2(Salary,ECode)values(@salary,@Ecode); End try Begin Catch -- your message should return in out put parameter end catch End step 4 - Execute the procedure with the below code Exec Insert_tables 'abc','C001',5000.00 step 5- select query is given below select * from test1 select * from test2

Devang Dave · Answer

Create PROCEDURE procemp @Empcode numeric , @FName nvarchar(100), @LName nvarchar(100), @sex nvarchar(100), @age numeric AS BEGIN Insert into abc(Empcode,FName, LName) values (@Empcode,@FName, @LName) Insert into xyz(Empcode,sex,age) values (@Empcode, @sex,@age) END GO

Deepak Khare · Answer

Persons id(pk) firstname lastname and Orders orderNumber orderdetails id(fk) create procedure @FirstName varchar(20),@OrderDetails varchar(20),@LastName varchar(20),@OrderNumber int as begin declare @Temp int insert into Hcl.dbo.a values(@FirstName,@LastName); select @Temp=Id from Hcl.dbo.a where FirstName=@FirstName insert into Hcl.dbo.b values(@Temp,@OrderDetails,@OrderNumber)

Vishal Jadav · Answer

Hi, According to my openion. Create after insert trigger for first table and save the same data from "inserted" table to new table using stored procedure. Hope this helps. Thanks.

Amit Kumar · Answer

hi, I think you want to insert single procedure then use it. Create Procedure InsertData ( @ID INT , @ Name NVARCHAR(100)= Null , @Age NVARCHAR(100)= Null , @Address NVARCHAR(100)= Null , @FName NVARCHAR(100)= Null , @MName NVARCHAR(100)= Null ) AS BEGIN DECLARE @GETQID INT IF EXISTS ( SELECT * FROM PRIMARYTABLE WHERE ID=@ID ) BEGIN ---WRITE YOUR UPDATE QUERY END ELSE BEGIN INSERT INTO PRIMARYTABLENAME ( NAME , AGE, ADDRESS) VALUES (@ Name ,@Age,@Address) SET @GETQID =@@IDENTITY ----@@IDENTITY RETURN LAST INSERTED ID WHICH YOU REQUIRED FOR SAVE IN ANOTHER TABLE END ---iNSERT DATA IN ANOTHER TABLE ON THE BEHALF OF @GETQID INSERT INTO TABLEB (MASTERID,FATHERNAME,MOTHERNAME) VALUES (@GETQID,@FName,@MName) END

Sandy · Answer

HI Seshu, you can use identity function to get last inserted id. @@IDENTITY if issue not resolved than kindly share more information that will help in troubleshooting. Thanks Sandy

Nilesh Patil · Answer

Hi, Refer this http://www.c-sharpcorner.com/blogs/how-to-insert-multiple-values-in-multiple-table-using-stored-procedure

How to insert data into two diffrent tables using sp in sql

Answers (8)