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

You are viewing reply posted by: pandeyism one month ago.
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