SP that involves inserting new identity into another table

[rewdee]

I want to create a SP that inserts into one table and then makes another insert into another table with an autoincrement ID into a second table. Something Like:

CREATE PROCEDURE AddMotor(
	@serialNumber varchar(50),
	@Location varchar(40),
	@MotorDesc varchar(100),
	@Manufacturer varchar(50),
	@Model varchar(20),
	@HP varchar(25),
	@RPM varchar(25),
	@ASECode varchar(50),
	)
AS
INSERT INTO tblMotors(serialNumber, EquipNum, MotorDesc,Manufacturer, Model, HP, RPM, )
VALUES(@serialNumber, @EquipNum, @MotorDesc, @Manufacturer, @Model, @HP, @RPM, )

INSERT INTO tblMotorStatus
    (EquipID, Status,sn,EquipmentNum,Archive)
    Values
    ([b]ID[/b],'Spare',@serialNumber,'Spare',0) 
GO

Trouble is How do I get the ID number from tblMotors? I could add a trigger on tblMotors but would rather do it on this one step.

Any ideas, suggestion would be appreciated.

Thanks,
Rewdee

 

[swampBoogie]

CREATE PROCEDURE AddMotor(
    @serialNumber varchar(50),
    @Location varchar(40),
    @MotorDesc varchar(100),
    @Manufacturer varchar(50),
    @Model varchar(20),
    @HP varchar(25),
    @RPM varchar(25),
    @ASECode varchar(50),
    )
AS
set nocount on
INSERT INTO tblMotors(serialNumber, EquipNum, MotorDesc,Manufacturer, Model, HP, RPM, )
VALUES(@serialNumber, @EquipNum, @MotorDesc, @Manufacturer, @Model, @HP, @RPM, )

INSERT INTO tblMotorStatus
    (EquipID, Status,sn,EquipmentNum,Archive)
    Values
    (scope_identity(),'Spare',@serialNumber,'Spare',0)
GO

 

[ESquared]

You might want to add some error handling. If the two inserts must perform as a unit and it would be a data error for the record to exist in tblMotors but not in tblMotorStatus, then do it inside a transaction so the two inserts perform atomically:

DECLARE
   @Rows int,
   @Err int

BEGIN TRANSACTION
INSERT INTO tblMotors ...
SELECT @Err = @@Error, @Rows = @@ROWCOUNT
IF @Rows = 1 AND @Err = 0 BEGIN
   INSERT INTO tblMotorStatus ...
   SELECT @Err = @@Error, @Rows = @@ROWCOUNT
END
IF @Rows = 1 AND @Err = 0 BEGIN
   COMMIT TRANSACTION
END
ELSE BEGIN
   ROLLBACK TRANSACTION
END

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[JohnE66]

You can add an extra column in the select into clause. Hopefully the following example will help.

SELECT IDENTITY(int, 1, 1) AS id, *
INTO new_table
FROM old_table

This is ok if there is not an indentity column in old_table

We are trying to work out how to do it if there is...