How to pass auto-incremented id into another table

Question

I have two tables named user_info and food_info, user_info have an auto-incremented attribute named user_id. now I want to insert into two tables at once through a transactions like shown in the below picture, I have done this manually, now I want to achieve this through transaction query. Note that food_info's user_id is not auto-incremented.

Possible duplicates http://stackoverflow.com/questions/4565195/mysql-how-to-insert-into-multiple-tables-with-foreign-keys — Che-Chia Chang, Jan 11 '17 at 07:09
Are you using MySQL or MS SQL Server? (Don't tag products not involved.) — jarlh, Jan 11 '17 at 08:03

score 0 · Accepted Answer · answered Jan 15 '17 at 17:50

 -- Declare Variables
 DECLARE @ErrorMessage NVARCHAR(4000),
    @ErrorSeverity INT,
    @ErrorState INT,
    @Last_Inserted_ID INT;

-- Try Catch Block
 BEGIN TRY

-- Start Transaction
  BEGIN TRANSACTION
    INSERT INTO [dbo].[user_info]
            ([user_name]
            ,[pass])
        VALUES
               ('U11'
               ,'U12')

    -- Returns the last identity value inserted
    SET @Last_Inserted_ID = @@IDENTITY;

    INSERT INTO [dbo].[food_info]
               ([user_id]
               ,[food_type])
         VALUES
               (@Last_Inserted_ID
               ,'Food1')

    PRINT 'Values inserted successfully...'

 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
    ROLLBACK TRANSACTION
END CATCH

How to pass auto-incremented id into another table

1 Answers1