T-SQL_同时往两张有关联的表增加数据

SET IMPLICIT_TRANSACTIONS ON ;
DECLARE @SID INT 
DECLARE @MYERROR INT
SET @MYERROR=0  
DECLARE @MaterialID1 INT 
DECLARE @MaterialID2 INT 
DECLARE @MaterialID3 INT 
DECLARE @DeliveredNetQty DECIMAL 
DECLARE @SONumber INT 

SET @MaterialID1 = 277212783;
SET @MaterialID2 = 266174981;
SET @MaterialID3 = 255675981;

SET @DeliveredNetQty = 6.00;
SET @SONumber = 5063047;

  
INSERT INTO [dbo].[SmartLogixSalesSyncs]
           ([AssignedDate]
           ,[AssignedDriver]
           ,[AssignedTruckCapacity]
           ,[AssignedTruckCode]
           ,[AssignedTruckId]
           ,[AssignedTruckVIN]
           ,[CreateDateTime]
           ,[ProcessTime]
           ,[RequiredBeginDateTime]
           ,[RequiredEndDateTime]
           ,[SalesOrderStatus]
           ,[ScheduledEndDeliveryDateTime]
           ,[ScheduledStartDeliveryDateTime]
           ,[SoNumber]
           ,[Status])
     VALUES
           (GETDATE(),NULL,0.00,'7811-01',NULL,NULL,GETDATE(),GETDATE(),NULL,NULL,10,NULL,NULL,@SONumber,NULL)

SELECT @SID = SCOPE_IDENTITY() --（获取刚插入的主键）
--第一条
INSERT INTO [dbo].[SmartLogixSalesDetailsSync]
           ([DeliveredByDriverName]
           ,[DeliveredByTruckId]
           ,[DeliveredDateTime]
           ,[DeliveredNetQty]
           ,[DeliveryStartDateTime]
           ,[LineItemNumber]
           ,[SAP_Material_ID]
           ,[SmartLogixSalesSync_ID]
           ,[AssignedDriverId])
     VALUES
           ('JOHN',123,GETDATE(),5.00,GETDATE(),1,@MaterialID1,@SID,NULL)
--第二条
INSERT INTO [dbo].[SmartLogixSalesDetailsSync]
           ([DeliveredByDriverName]
           ,[DeliveredByTruckId]
           ,[DeliveredDateTime]
           ,[DeliveredNetQty]
           ,[DeliveryStartDateTime]
           ,[LineItemNumber]
           ,[SAP_Material_ID]
           ,[SmartLogixSalesSync_ID]
           ,[AssignedDriverId])
     VALUES
           ('JOHN',123,GETDATE(),@DeliveredNetQty,GETDATE(),2,@MaterialID2,@SID,NULL)
--第三条
INSERT INTO [dbo].[SmartLogixSalesDetailsSync]
           ([DeliveredByDriverName]
           ,[DeliveredByTruckId]
           ,[DeliveredDateTime]
           ,[DeliveredNetQty]
           ,[DeliveryStartDateTime]
           ,[LineItemNumber]
           ,[SAP_Material_ID]
           ,[SmartLogixSalesSync_ID]
           ,[AssignedDriverId])
     VALUES
           ('JOHN',123,GETDATE(),@DeliveredNetQty,GETDATE(),3,@MaterialID3,@SID,NULL)




SET @MYERROR=@MYERROR+@@ERROR

IF @MYERROR>0  --如果出错  
  BEGIN   
    ROLLBACK TRANSACTION  
  END  
ELSE           --没有出现问题  
  BEGIN    
    COMMIT TRANSACTION  
  END
SET IMPLICIT_TRANSACTIONS OFF;