Insert records in multiple Tables using Stored Procedure in SQL Server

Last Reply one month ago By pandeyism

Posted one month ago

i wanted to write SP to insert record in multiple tables.

I have the following code: 

IF (NOT EXISTS(select DATA_FLOW_ID from [DATA_FLOW_MST_T] where FLOW='D0135') )
BEGIN 
--Insert Data Flow

INSERT INTO [dbo].[DATA_FLOW_MST_T]
           ([FLOW],[FLOW_VERSION_NO],[FLOW_VERSION_DESCRIPTION],[FLOW_NAME],[FLOW_DESCRIPTION],[FLOW_OWNER],[FLOW_EXTERNAL_NOTES],[VERSION_CREATED],[FLOW_DELETED],[FLOW_NUMBER],[ENTERED_BY],[ENTERED_ON],[GroupCount],[IS_FLOW_CREATED],[MIN_GROUP_COUNT],[CHECK_GROUP_COUNT],[RESPONSE_TIME])
     VALUES
           ('D0135','1' ,'0' ,'Rejection of Withdrawal Process Flow','Withdrawal of Registration process has been rejected from MPAS to New Supplier'
           ,'MRA',null,'11.1','0','360',(select USER_ID from SEC_USERS_MST_T where USER_FIRST_NAME='Atul'),GETDATE(),'3','1',null,'0',null)

--Insert Data Flow Group

INSERT INTO [dbo].[DATA_FLOW_GROUP_MST_T]
           ([GROUP_NAME],[DATA_FLOW_ID],[ENTERED_BY],[ENTERED_ON],[ITEM_COUNT],[GROUP_SEQUENCE],[IS_COMMON],[WHERE],[CONDITION],[GROUP_DESC])
     VALUES
           ('29I',(select DATA_FLOW_ID from [DATA_FLOW_MST_T] where FLOW='D0360'),(select USER_ID from SEC_USERS_MST_T where USER_FIRST_NAME='Atul'),GETDATE(),'1','1','1'
           ,null,null,'File Sequence Number')

INSERT INTO [dbo].[DATA_FLOW_GROUP_MST_T]
           ([GROUP_NAME],[DATA_FLOW_ID],[ENTERED_BY],[ENTERED_ON],[ITEM_COUNT],[GROUP_SEQUENCE],[IS_COMMON],[WHERE],[CONDITION],[GROUP_DESC])
     VALUES
           ('30I',(select DATA_FLOW_ID from [DATA_FLOW_MST_T] where FLOW='D0360'),(select USER_ID from SEC_USERS_MST_T where USER_FIRST_NAME='Atul'),GETDATE(),'7','2','1'
           ,null,null,'Withdrawal Rejection')

INSERT INTO [dbo].[DATA_FLOW_GROUP_MST_T]
           ([GROUP_NAME],[DATA_FLOW_ID],[ENTERED_BY],[ENTERED_ON],[ITEM_COUNT],[GROUP_SEQUENCE],[IS_COMMON],[WHERE],[CONDITION],[GROUP_DESC])
     VALUES
           ('34I',(select DATA_FLOW_ID from [DATA_FLOW_MST_T] where FLOW='D0360'),(select USER_ID from SEC_USERS_MST_T where USER_FIRST_NAME='Atul'),GETDATE(),'1','3','1'
           ,null,null,'Rejection Reasons')
END
GO

How to write the script for above?

any help will be appreciated

Posted one month ago Modified on one month ago

Hi chetan,

Refer below sample query.

SQL

CREATE PROCEDURE InsertMultipleTable
    @Id INT
AS 
BEGIN
    IF NOT EXISTS(SELECT CustomerId FROM Customers WHERE CustomerId = @Id)
        INSERT INTO Customers VALUES('Test','Test')
        SELECT * FROM Customers
        
    IF NOT EXISTS(SELECT CustomerId FROM CustomerTest WHERE CustomerId = @Id)
        INSERT INTO CustomerTest VALUES('Test1','Test1')
        SELECT * FROM CustomerTest
END